Search code examples
kdbq-lang

q - filter by group frequencies

I would like to filter a table by a condition on the frequency of groups in a certain column. Example:

Given table

tmp:([] id:`a`a`b`b`b`c; c2:1 2 3 4 5 6)

first find the frequencies of each group

ce:count each group tmp[`id]

then select the rows in tmp where the id's group count is more than 1

select from tmp where id in where ce > 1

id  c2
a   1
a   2
b   3
b   4
b   5
(row id=`c is gone because it appeared only once)

How can this be done more elegant?

Thanks

Solution

  • You can use fby e.g.

    q)select from tmp where 1<(count;i) fby id
id c2
-----
a  1
a  2
b  3
b  4
b  5