How to efficiently exclude not null values of list of floats and ints from tables for multiple tables and dynamic cols
How to efficiently exclude not null values of list of floats and ints from tables for multiple tables and dynamic cols
tab1:update volume:0n from ([] date:2024.01.01+til 10;sym:10?`appl`msgt`googl;volume:10#(enlist 0.5+300?til 100);price:10?10.5) where i in (1;5;8);
tab2:update price:0n from ([] date:2024.01.01+til 10;sym:10?`appl`msgt`googl;spread:10#(enlist 0.5+300?til 100);price:10?10.5) where i in (1;5;8);
//columns to check
(`tab1;`volume`price);
(`tab2;`spread`price);
How do we efficiently check if we have nulls present for the columns mentioned above for each table.
If any one the column in tab1 is null from the list then we dont want to display that row. example : select from tab1 where not null volume & not null price // volume is of type float list here, price in float.
Note : Table can be any number of tables and any number of cols and the type of column we need to check can be int, float, list of int, list if float.
Solution
Something like this? (using a functional select)
q)select from tab1 where not any each null volume,not any each null price
date sym volume ..
---------------------------------------------------------..
2024.01.01 appl 84.5 63.5 93.5 54.5 38.5 97.5 88.5 58.5 ..
2024.01.03 msgt 84.5 63.5 93.5 54.5 38.5 97.5 88.5 58.5 ..
2024.01.04 msgt 84.5 63.5 93.5 54.5 38.5 97.5 88.5 58.5 ..
2024.01.05 msgt 84.5 63.5 93.5 54.5 38.5 97.5 88.5 58.5 ..
2024.01.07 googl 84.5 63.5 93.5 54.5 38.5 97.5 88.5 58.5 ..
2024.01.08 appl 84.5 63.5 93.5 54.5 38.5 97.5 88.5 58.5 ..
2024.01.10 appl 84.5 63.5 93.5 54.5 38.5 97.5 88.5 58.5 ..
q)parse"select from tab1 where not any each null volume,not any each null price"
?
`tab1
,((~:;(k){x'y};max$["b"];(^:;`volume)));(~:;(k){x'y};max$["b"];(^:;`price))))
0b
()
q)?[tab1;{(not;(each;any;(null;x)))}each`volume`price;0b;()]
date sym volume ..
---------------------------------------------------------..
2024.01.01 appl 84.5 63.5 93.5 54.5 38.5 97.5 88.5 58.5 ..
2024.01.03 msgt 84.5 63.5 93.5 54.5 38.5 97.5 88.5 58.5 ..
2024.01.04 msgt 84.5 63.5 93.5 54.5 38.5 97.5 88.5 58.5 ..
2024.01.05 msgt 84.5 63.5 93.5 54.5 38.5 97.5 88.5 58.5 ..
2024.01.07 googl 84.5 63.5 93.5 54.5 38.5 97.5 88.5 58.5 ..
2024.01.08 appl 84.5 63.5 93.5 54.5 38.5 97.5 88.5 58.5 ..
2024.01.10 appl 84.5 63.5 93.5 54.5 38.5 97.5 88.5 58.5 ..
q)?[tab1;{(not;(each;any;(null;x)))}each`volume`price;0b;()]~select from tab1 where not any each null volume,not any each null price
1b
This should work for multiple table/column arguments
q){?[x;{(not;(each;any;(null;x)))}each y;0b;()]}.'((`tab1;`volume`price);(`tab2;`spread`price))
+`date`sym`volume`price!(2024.01.01 2024.01.03 2024.01.04..
+`date`sym`spread`price!(2024.01.01 2024.01.03 2024.01.04..
To cover the empty list case:
q)tab1:update volume:count[i]#enlist 0#0f from ([] date:2024.01.01+til 10;sym:10?`appl`msgt`googl;volume:10#(enlist 0.5f+300?til 100);price:10?10.5) where i in (1;5;8);
q)select from tab1 where not any'[null volume]|volume~'0#'volume
date sym volume ..
---------------------------------------------------------..
2024.01.01 googl 40.5 30.5 80.5 10.5 78.5 68.5 7.5 65.5 5..
2024.01.03 msgt 40.5 30.5 80.5 10.5 78.5 68.5 7.5 65.5 5..
2024.01.04 msgt 40.5 30.5 80.5 10.5 78.5 68.5 7.5 65.5 5..
2024.01.05 msgt 40.5 30.5 80.5 10.5 78.5 68.5 7.5 65.5 5..
2024.01.07 appl 40.5 30.5 80.5 10.5 78.5 68.5 7.5 65.5 5..
2024.01.08 appl 40.5 30.5 80.5 10.5 78.5 68.5 7.5 65.5 5..
2024.01.10 appl 40.5 30.5 80.5 10.5 78.5 68.5 7.5 65.5 5..
q)parse"select from tab1 where not any'[null volume]|volume~'0#'volume"
?
`tab1
,,(~:;(|;((';max$["b"]);(^:;`volume));((';~);`volume;((';#);0;`volume))))
0b
()
q)?[tab1;{(not;(|;((';any);(null;x));((';~);x;((';#);0;x))))}each`volume`price;0b;()]
date sym volume ..
---------------------------------------------------------..
2024.01.01 googl 40.5 30.5 80.5 10.5 78.5 68.5 7.5 65.5 5..
2024.01.03 msgt 40.5 30.5 80.5 10.5 78.5 68.5 7.5 65.5 5..
2024.01.04 msgt 40.5 30.5 80.5 10.5 78.5 68.5 7.5 65.5 5..
2024.01.05 msgt 40.5 30.5 80.5 10.5 78.5 68.5 7.5 65.5 5..
2024.01.07 appl 40.5 30.5 80.5 10.5 78.5 68.5 7.5 65.5 5..
2024.01.08 appl 40.5 30.5 80.5 10.5 78.5 68.5 7.5 65.5 5..
2024.01.10 appl 40.5 30.5 80.5 10.5 78.5 68.5 7.5 65.5 5..