select with combinational conditions

Community Support
1

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

For example, for the table below:

 

 

q)flip datedata!(2023.05.20 2023.05.20 2023.05.20 2023.05.19 2023.05.19 2023.05.19;acbbbc) 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

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:

 

l:((2023.05.20;ab);(2023.05.19;enlist b);(2023.05.18;cda);(2023.05.17;da)); tab:flip datedata!(2023.05.20 2023.05.20 2023.05.20 2023.05.19 2023.05.19 2023.05.19;acbbbc); 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

3

If you went directly in to writing what you ask:

 

q)t:flip datedata!(2023.05.20 2023.05.20 2023.05.20 2023.05.19 2023.05.19 2023.05.19;acbbbc) q)select from t where any (and[date=2023.05.20;data in ab];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 ab];and[date=2023.05.19;data in enlist b])" ? t ,,(max$["b"];(enlist;(&;(=;date;2023.05.20);(in;data;,ab));(&;(=;date;2023.05.19);(in;data;(enlist;,b))))) 0b () q)?[t;enlist(any;(enlist;(and;(=;date;2023.05.20);(in;data;enlist ab));(and;(=;date;2023.05.19);(in;data;(enlist;enlistb)))));0b;()] date data --------------- 2023.05.20 a 2023.05.20 b 2023.05.19 b 2023.05.19 b q)f:((2023.05.20;ab);(2023.05.19;enlist b);(2023.05.18;cda);(2023.05.17;da)) 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 datesyms!flip f date syms ----------------- 2023.05.20 ab 2023.05.19 ,b 2023.05.18 cda 2023.05.17 da q)raze {select from t where date=xdate,data in xdata} 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

 

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 datedata!(2023.05.20 2023.05.20 2023.05.20 2023.05.19 2023.05.19 2023.05.19;acbbbc) 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:abb) 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:(ab;enlist b;cda;da)) q)filter date data ----------------- 2023.05.20 ab 2023.05.19 ,b 2023.05.18 cda 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