I am new to KDB. I have a table in following format:
id date name order
34 2020.01.20 John 10
23 2020.01.20 John -20
21 2020.01.20 John 30
43 2020.01.20 John -400
44 2020.01.20 Dan -6483
22 2020.01.20 Dan 8796
The sample table can be created as follows:
t:([]id:(34, 23, 21, 43, 44, 22); date:(2020.01.20; 2020.01.20; 2020.01.20; 2020.01.20; 2020.01.20; 2020.01.20); name:(`John`John`John`John`Dan`Dan); order:(10, -20, 30, -400, -6483, 8796));
I want all the subsets of orders possible for any given date and name in the below format. Now the order column below is sum of order value for id and all the ids.
id date name order ids
34 2020.01.20 John 10 0n
34 2020.01.20 John -10 23
34 2020.01.20 John 40 21
34 2020.01.20 John -390 43
34 2020.01.20 John 20 23, 21
34 2020.01.20 John -360 21, 43
34 2020.01.20 John -380 23, 21, 43
23 2020.01.20 John -20 0n
23 2020.01.20 John -10 34
23 2020.01.20 John 10 21
23 2020.01.20 John -420 43
23 2020.01.20 John 20 34, 21
23 2020.01.20 John -390 21, 43
23 2020.01.20 John -380 34, 21, 43
21 2020.01.20 John 30 0n
21 2020.01.20 John 40 34
21 2020.01.20 John 10 23
21 2020.01.20 John -370 43
21 2020.01.20 John 20 34, 23
21 2020.01.20 John 20 23, 43
21 2020.01.20 John -380 34, 23, 43
43 2020.01.20 John -400 0n
43 2020.01.20 John -390 34
43 2020.01.20 John -420 23
43 2020.01.20 John -370 21
43 2020.01.20 John -410 34, 23
43 2020.01.20 John -390 23, 21
43 2020.01.20 John -380 34, 23, 21
44 2020.01.20 Dan -6483 0n
44 2020.01.20 Dan 2313 22
22 2020.01.20 Dan 8796 0n
22 2020.01.20 Dan 2313 44
This gets you part of the way, though this approach has an extra combination that you seem to exclude (you can exclude these afterwards if need be):
q)comb:{$[type b:(count[a:x except y]-1)(01b cross)/01b;(`long$();a);a where each b]};
q)update sum each order from ungroup ungroup select id,order:(order,/:'order i?comb[i]each i),ids:id i?comb[i]each i by date,name from t
date name id order ids
---------------------------------
2020.01.20 Dan 44 -6483 `long$()
2020.01.20 Dan 44 2313 ,22
2020.01.20 Dan 22 8796 `long$()
2020.01.20 Dan 22 2313 ,44
2020.01.20 John 34 10 `long$()
2020.01.20 John 34 -390 ,43
2020.01.20 John 34 40 ,21
2020.01.20 John 34 -360 21 43
2020.01.20 John 34 -10 ,23
2020.01.20 John 34 -410 23 43
2020.01.20 John 34 20 23 21
2020.01.20 John 34 -380 23 21 43
2020.01.20 John 23 -20 `long$()
2020.01.20 John 23 -420 ,43
...