How to efficiently exclude not null values of list of floats and ints from tables for multiple tables and dynamic cols

Question

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.

Answer 1

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..