kdb: Tabulation by 2 keys, including combos with 0 counts

Question

When one does something like select Count:count i by Id,sym from T we will of course get a simple keyed table much like the following:

t1:`Id`sym xkey ([]Id:`A`A`B`B;sym:`sym1`sym2`sym2`sym3;Count:3 4 8 8)

Note though that there is no entry with (Id=`A, sym=`sym3) or (Id=`b,sym=`sym2). In my tabulation, I would like to include these null combos, where the counts are filled in with 0s. The ouput should look something like this:

t2:`Id`sym xkey([]Id:`A`A`A`B`B`B;sym:`sym1`sym2`sym3`sym1`sym2`sym3;Count:3 4 0 0 8 8)

Cleanest way to do this?

Answer 1

Couple of ways to do this:

q)0^((select distinct Id from t1) cross select distinct sym from t1)#t1
Id sym | Count
-------| -----
A  sym1| 3
A  sym2| 4
A  sym3| 0
B  sym1| 0
B  sym2| 8
B  sym3| 8

or (essentially the same thing)

q)0^2!lj[;t1] (select distinct Id from t1) cross select distinct sym from t1
Id sym | Count
-------| -----
A  sym1| 3
A  sym2| 4
A  sym3| 0
B  sym1| 0
B  sym2| 8
B  sym3| 8

Note - the "zero fill" only works here if Count is the only unkeyed column. Otherwise you'd have to do an update 0^Count from table