select with combinational conditions

Community Support
,
1

https://learninghub.kx.com/forums/topic/select-with-combinational-conditions

Hi, is there a way to make select statements with multiple conditions in combinations?

For example, for the table below:

q)flip `date`data!(2023.05.20 2023.05.20 2023.05.20 2023.05.19 2023.05.19 2023.05.19;`a`c`b`b`b`c) 
date      data 
--------------- 
2023.05.20 a 
2023.05.20 c 
2023.05.20 b 
2023.05.19 b 
2023.05.19 b 
2023.05.19 c

 

I would like to select rows with

(date=2023.05.20 and data in ab) or

(date=2023.05.19 and data in enlist `b)

i.e. the results should be

 

date      data 
--------------- 
2023.05.20 a 
2023.05.20 b 
2023.05.19 b 
2023.05.19 b

 

I have a list of these dates/data combinations, is there an easy way to do such filter?

i.e.

2023.05.20 ab

2023.05.19 enlist `b

2023.05.18 cd`a

2023.05.17 da

…

 

Thanks.

 

2

If you went directly in to writing what you ask:

 

q)t:flip `date`data!(2023.05.20 2023.05.20 2023.05.20 2023.05.19 2023.05.19 2023.05.19;`a`c`b`b`b`c) 
q)select from t where any (and[date=2023.05.20;data in `a`b];and[date=2023.05.19;data in enlist `b])
date data 
--------------- 
2023.05.20 a 
2023.05.20 b 
2023.05.19 b 
2023.05.19 b 

q)parse "select from t where any (and[date=2023.05.20;data in `a`b];and[date=2023.05.19;data in enlist `b])" 
? 
`t 
,,(max$["b"];(enlist;(&;(=;`date;2023.05.20);(in;`data;,`a`b));(&;(=;`date;2023.05.19);(in;`data;(enlist;,`b))))) 
0b 
() 
q)?[`t;enlist(any;(enlist;(and;(=;`date;2023.05.20);(in;`data;enlist `a`b));(and;(=;`date;2023.05.19);(in;`data;(enlist;enlist`b)))));0b;()] 
date data 
--------------- 
2023.05.20 a 
2023.05.20 b 
2023.05.19 b 
2023.05.19 b 

q)f:((2023.05.20;`a`b);(2023.05.19;enlist `b);(2023.05.18;`c`d`a);(2023.05.17;`d`a)) 
q)?[`t;(enlist(any;enlist,{(and;(=;`date;x 0);(in;`data;enlist x 1))}each f));0b;()] 
date data 
--------------- 
2023.05.20 a 
2023.05.20 b 
2023.05.19 b 
2023.05.19 b

 

Much better if the data is on disk and partitioned by date would be to iterate over each date and not each filter:

 

q)f:flip `date`syms!flip f 
date syms 
----------------- 
2023.05.20 `a`b 
2023.05.19 ,`b 
2023.05.18 `c`d`a 
2023.05.17 `d`a 

q)raze {select from t where date=x`date,data in x`data} peach 0!select distinct raze data by date from f 
date data 
--------------- 
2023.05.19 b 
2023.05.19 b 
2023.05.20 a 
2023.05.20 b

 

3

Assuming your initial table can grow to a large size, probably best to only query it once to get the dates and symbols you’re interested in:

 

q)l:((2023.05.20;`a`b);(2023.05.19;enlist `b);(2023.05.18;`c`d`a);(2023.05.17;`d`a)); 
q)tab:flip `date`data!(2023.05.20 2023.05.20 2023.05.20 2023.05.19 2023.05.19 2023.05.19;`a`c`b`b`b`c); 
q)subset:select from tab where date in l[;0],data in raze l[;1]; 
q)raze{select from y where date=x[0],data in x[1]}[;subset]each l 
date      data 
--------------- 
2023.05.20 a 
2023.05.20 b 
2023.05.19 b 
2023.05.19 b

 

There’s probably a more efficient way but this should do it

4

Something worth adding to the previous answers: If you can transform your filter into a table then the select is much easier. To illustrate

q)t:flip `date`data!(2023.05.20 2023.05.20 2023.05.20 2023.05.19 2023.05.19 2023.05.19;`a`c`b`b`b`c) 
q)t 
date      data 
--------------- 
2023.05.20 a 
2023.05.20 c 
2023.05.20 b 
2023.05.19 b 
2023.05.19 b 
2023.05.19 c 

q)select from t where ([] date;data) in ([] date:2023.05.20 2023.05.20 2023.05.19;data:`a`b`b) 
date      data 
--------------- 
2023.05.20 a 
2023.05.20 b 
2023.05.19 b 
2023.05.19 b 

q)filter:([] date:2023.05.20 2023.05.19 2023.05.18 2023.05.17; data:(`a`b;enlist `b;`c`d`a;`d`a)) 
q)filter 
date        data 
----------------- 
2023.05.20 `a`b 
2023.05.19 ,`b 
2023.05.18 `c`d`a 
2023.05.17 `d`a 

q)ungroup filter 
date      data 
--------------- 
2023.05.20 a 
2023.05.20 b 
2023.05.19 b 
2023.05.18 c 
2023.05.18 d 
2023.05.18 a 
2023.05.17 d 
2023.05.17 a 

q)t1:ungroup filter 
q)select from t where ([] date;data) in t1 
date      data 
--------------- 
2023.05.20 a 
2023.05.20 b 
2023.05.19 b 
2023.05.19 b