select with combinational conditions

[powerpeanuts]

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 `a`b) 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 `a`b

2023.05.19 enlist `b

2023.05.18 `c`d`a

2023.05.17 `d`a

...

Thanks.

[eohara_kdb]

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;`a`b);(2023.05.19;enlist `b);(2023.05.18;`c`d`a);(2023.05.17;`d`a));
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);
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

[rocuinneagain · Accepted solution]

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

[unterrainer_ale]

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