Hi, I am trying to use fby to aggregate based on bunch of columns(Trigger date,level date,ccypair).How can we use fby when I want one column(leveldate) to mandatorily unequal during group formation and rest trigger date and ccypair matching? Essentially I want to avoid scenario where a specific column value say leveldate is same during group by. Regards, Harsh
Hi,
I am not 100% sure I understant what you want to do but this example might help:
q)t:(k1:20?ab;k2:20?cd;v:20?3;id:key 20)
q)t
k1 k2 v id
----------
a c 0 0
a c 1 1
b c 0 2
b d 0 3
b c 1 4
b c 1 5
b d 1 6
b c 0 7
a d 2 8
a c 2 9
a c 2 10
a d 0 11
b c 1 12
a d 1 13
b c 0 14
b c 1 15
b c 1 16
b d 1 17
b c 2 18
a d 2 19
q)select v,id by k1,k2 from t
k1 k2| v id
-----| ----------------------------------------
a c | 0 1 2 2 0 1 9 10
a d | 2 0 1 2 8 11 13 19
b c | 0 1 1 0 1 0 1 1 2 2 4 5 7 12 14 15 16 18
b d | 0 1 1 3 6 17
q)select v,id by k1,k2 from t where ({key[count x] in x?distinct x};v) fby (k1;k2)
k1 k2| v id
-----| -------------
a c | 0 1 2 0 1 9
a d | 2 0 1 8 11 13
b c | 0 1 2 2 4 18
b d | 0 1 3 6
Regards,
Andras
You get a simmilar result by simply running:
q)select v,id by k1,k2 from ungroup select 1#id by k1,k2,v from t
k1 k2| v id
-----| -------------
a c | 0 1 2 0 1 9
a d | 0 1 2 11 13 8
b c | 0 1 2 2 4 18
b d | 0 1 3 6
Regards,
András