xgroup¶
Groups a table by values in selected columns
x xgroup y xgroup[x;y]Where
yis a table passed by valuexis a symbol atom or vector of column names iny
returns y grouped by x.
It is equivalent to doing a select … by on y, except that all the remaining columns are grouped without having to be listed explicitly.
q)`a`b xgroup ([]a:0 0 1 1 2;b:`a`a`c`d`e;c:til 5)
a b| c
---| ---
0 a| 0 1
1 c| ,2
1 d| ,3
2 e| ,4
q)\l sp.q
q)meta sp / s and p are both columns of sp
c | t f a
---| -----
s | s s
p | s p
qty| i
q)`p xgroup sp / group by column p
p | s qty
--| -------------------------------
p1| `s$`s1`s2 300 300
p2| `s$`s1`s2`s3`s4 200 400 200 200
p3| `s$,`s1 ,400
p4| `s$`s1`s4 200 300
p5| `s$`s4`s1 100 400
p6| `s$,`s1 ,100
q)select s,qty by p from sp / equivalent select statement
p | s qty
--| -------------------------------
p1| `s$`s1`s2 300 300
p2| `s$`s1`s2`s3`s4 200 400 200 200
p3| `s$,`s1 ,400
p4| `s$`s1`s4 200 300
p5| `s$`s4`s1 100 400
p6| `s$,`s1 ,100
q)ungroup `p xgroup sp / ungroup flattens the groups
p s qty
---------
p1 s1 300
p1 s2 300
p2 s1 200
p2 s2 400
p2 s3 200
p2 s4 200
p3 s1 400
..Duplicate keys or column names
Duplicate keys in a dictionary or duplicate column names in a table will cause sorts and grades to return unpredictable results.