How can I set conditions in the column selection of a functional select? Ex:
select sum X, sum X[where z=A],sum X[where z=B] by grouping from table
=
?[table; (); (enlist grouping)!(enlist grouping); (`Sum`SumA`SumB)!((sum;X);(sum X where z=A);(sum X where z=B))]
Thanks
q)tablegrouping z X------------3 B 81 C 22 C 43 B 12 A 63 B 02 C 51 A 31 B 72 B 9q)q)parse"select sum X, sum X[where z=A],sum X[where z=B] by grouping from table"?table()(,grouping)!,groupingXAB!((sum;X);(sum;(X;(&:;(=;z;A))));(sum;(X;(&:;(=;z;B)))))q)q)?[table;();enlist[grouping]!enlistgrouping;SumSumASumB!((sum;X);(sum;(X;(where;(=;z;enlistA))));(sum;(X;(where;(=;z;enlistB)))))]grouping| Sum SumA SumB--------| -------------1 | 12 3 72 | 24 6 93 | 9 0 9
On Saturday, 3 November 2018 09:00:40 UTC, Ben R wrote:
How can I set conditions in the column selection of a functional select? Ex:
select sum X, sum X[where z=A],sum X[where z=B] by grouping from table
=
?[table; (); (enlist grouping)!(enlist grouping); (`Sum`SumA`SumB)!((sum;X);(sum X where z=A);(sum X where z=B))]
Thanks
I guess a cleaner way of saying this is: functional select with a conditional sum
You can use “parse” for conversion of any select statement to parse tree (which is basically finctional select in form of Q q syntax tree)
Ex:
q) parse “select sum X, sum X[where z=A],sum X[where z=B] by grouping from table”
result:(?;table;();(enlist grouping)!enlist grouping;(XAB)!((sum;X);(sum;(X;(&:;(=;z;A))));(sum;(X;(&:;(=;z;`B))))))
functional select then: ?[`table;();(enlist `grouping)!enlist `grouping;(`X`A`B)!((sum;`X);(sum;(`X;(&:;(=;`z;`A))));(sum;(`X;(&:;(=;`z;`B)))))]
(you can replace “&:” with “where” and get same result, “&:” is just k form of “where” in q)
And alternative that I often use:
select sum X,sum X*z=A,sum X*z=B by grouping from table
Alternatively in functional select:
?[table;();b!b:enlistgroup;SumSumASumB!((sum;X);(sum;(*;(X;(=;z;A))));(sum;(*;(X;(=;z;B)))))]`