Hello,I often need to fill in zeros so that I have a value for every combinationof a set of keys.For example:q)t:([date:1 1 1 2 2; sym:`A`B`D`B`C] pl:5?1f)q)tdate sym| pl--------| ---------1 A | 0.30177231 B | 0.7850331 D | 0.53470962 B | 0.71117162 C | 0.411597q)2!`date`sym xasc update 0^pl from (0!t)uj (select distinct date from t)cross select distinct sym from tdate sym| pl--------| ---------1 A | 0.30177231 A | 01 B | 0.7850331 B | 01 C | 01 D | 0.53470961 D | 02 A | 02 B | 0.71117162 B | 02 C | 0.4115972 C | 02 D | 0Is there a more terse, idiomatic way to get the same result?Thanks,Josh
Oops.. I screwed up the example. The output I want isq)update 0^pl from uj[;t] 2!update pl:0f from (select distinct date from t)cross select distinct sym from tdate sym| pl--------| ---------1 A | 0.30177231 B | 0.7850331 D | 0.53470961 C | 02 A | 02 B | 0.71117162 D | 02 C | 0.411597On 30 November 2015 11:51 UTC, joshmyzie2@yandex.com wrote:> Hello,>> I often need to fill in zeros so that I have a value for every combination> of a set of keys.>> For example:>> q)t:([date:1 1 1 2 2; sym:`A`B`D`B`C] pl:5?1f)> q)t> date sym| pl> --------| ---------> 1 A | 0.3017723> 1 B | 0.785033> 1 D | 0.5347096> 2 B | 0.7111716> 2 C | 0.411597> q)2!`date`sym xasc update 0^pl from (0!t)uj (select distinct date from t)cross select distinct sym from t> date sym| pl> --------| ---------> 1 A | 0.3017723> 1 A | 0> 1 B | 0.785033> 1 B | 0> 1 C | 0> 1 D | 0.5347096> 1 D | 0> 2 A | 0> 2 B | 0.7111716> 2 B | 0> 2 C | 0.411597> 2 C | 0> 2 D | 0>>> Is there a more terse, idiomatic way to get the same result?>> Thanks,> Josh
q)update 0^pl from uj[;t] 2!update pl:0f from (select distinct date from t)cross select distinct sym from t
you can shorten the cross. and then another cross will give you null pl:
q)(cross[;].(‘[asc;distinct])’[(0!t)
datesym]) cross 0n1 `A 0n
1 `B 0n
1 `C 0n
1 `D 0n
2 `A 0n
2 `B 0n
2 `C 0n
2 `D 0n
so a shorter expression is:
q)(2!flip
datesympl!flip(cross[;].('[asc;distinct])'[(0!t)date`sym]) cross 0n) uj tdate sym| pl
--------| ---------
1 A | 0.3927524
1 B | 0.5170911
1 C |
1 D | 0.5159796
2 A |
2 B | 0.4066642
2 C | 0.1780839
2 D |
but select distinct may be faster and use less memory than (‘[asc;distinct])’[(0!t)datesym]) above
ta, jack
one way
q)((select distinct date from t)cross select distinct sym from t)pj t
Hi Josh,
Here is a generic method, for a table with any number of keys:
t:([date:1 1 1 2 2; sym:`A`B`D`B`C] pl:5?1f)
t
date sym| pl
--------| ---------
1 A | 0.1330435
1 B | 0.8999625
1 D | 0.2571
2 B | 0.0651849
2 C | 0.425422
pad:{0^k!x k:flip keys!flip (cross/)distinct each value flip key x}
pad t
date sym| pl
--------| ---------
1 A | 0.1330435
1 B | 0.8999625
1 D | 0.2571
1 C | 0
2 A | 0
2 B | 0.0651849
2 D | 0
2 C | 0.425422
The pad function extracts the distinct keys and creates a new table with the original information, inserted by key. The nulls are replaced with zeros.
Here is an example using a table with four key columns:
)t:([date:1 1 1 2 2; sym:`A`C`A`B`A; key3:“UUMUM”; key4:7 8 7 9 7] pl:5?1f)
q)pad t
date sym key3 key4| pl
------------------| ---------
1 A U 7 | 0.9921317
1 A U 8 | 0
1 A U 9 | 0
1 A M 7 | 0.3933534
1 A M 8 | 0
1 A M 9 | 0
1 C U 7 | 0
1 C U 8 | 0.4865121
1 C U 9 | 0
1 C M 7 | 0
1 C M 8 | 0
1 C M 9 | 0
1 B U 7 | 0
1 B U 8 | 0
1 B U 9 | 0
1 B M 7 | 0
1 B M 8 | 0
1 B M 9 | 0
2 A U 7 | 0
2 A U 8 | 0
..
Regards,
Liam,
AquaQ Analyics