Search code examples
kdb

how to use tab2 rows as col name and values to form where clause for tab1 and execute it

I have a table tab2

tab2:([] countries:`symbol$(); filter_cols:`symbol$(); filter_ops:`symbol$(); filter_vals:`symbol$(); syms:`symbol$());
insert[`tab2](`$"United States"; `event; `in; `$"Fed Interest Rate Decision"; `FDTR);
insert[`tab2](`$"United States"; `event; `in; `; `USOFF);
insert[`tab2](`$"United States"; `incident; `in; `$"Non Farm Payrolls"; `$"NFP TCH");
insert[`tab2](`$"United States"; `event; `in; `$"Fed Beige Book"; `);
insert[`tab2](`$"United States"; `event; `in; `$"Fed Beige Book"; `FDTR);
insert[`tab2](`$"United States"; `event; `in; `$"Fed Beige Book"; `$"USD CALENDAR");
insert[`tab2](`$"United States"; `event; `in; `$"FOMC Minutes"; `FDTR);

enter image description here

and have another table tab1

//generate sample table
n: 100;  // Number of rows in the table
tab:([] date: .z.d - n?30; event: n?(`$"Fed Interest Rate Decision";`;`$"Non Farm Payrolls";`$"Fed Beige Book";`$"FOMC Minutes"); incident: n?(`$"Non Farm Payrolls";`$"Farm Payrolls"); sym: n?(`FDTR;`;`$"USD CALENDAR";`$"NFP TCH";`USOFF); price: "f"$n?100.5; recv_time: n?10:00:00.000 + n?10000000; is_active: "b"$n?1 0);
10#tab

enter image description here

Now, I want to query tab1 using tab2 something like below

select from tab where tab2[`filter_cols] tab2[`filter_ops] tab2[`filter_vals], sym in tab2[`syms]

So it will query tab1 like below and get column name (event/incident), (in/like), (values) from tab2

select from tab where event in `$"Fed Interest Rate Decision", sym in `FDTR
select from tab where event in `, sym in `USOFF
select from tab where incident in `$"Non Farm Payrolls", sym in `$"NFP TCH"
select from tab where event in `$"Fed Beige Book", sym in `
select from tab where event in `$"Fed Beige Book", sym in `FDTR
select from tab where event in `$"Fed Beige Book", sym in `$"USD CALENDAR"
select from tab where event in `$"FOMC Minutes", sym in `FDTR
Solution

  • You'll need to use a functional select here if you want to use column names that are dynamically produced.

    q){[w;x;y;z]?[tab;((get string w;x;enlist y);(in;`sym;enlist z));0b;()]}.'flip tab2`filter_ops`filter_cols`filter_vals`syms
+`date`event`incident`sym`price`recv_time`is_active!(2023.05.09 2023.05.27 2023.05.23 2023.05.16 ..
+`date`event`incident`sym`price`recv_time`is_active!(2023.05.06 2023.05.06 2023.05.01 2023.05.14;..
+`date`event`incident`sym`price`recv_time`is_active!(2023.05.02 2023.05.01 2023.05.11 2023.05.26 ..
+`date`event`incident`sym`price`recv_time`is_active!(2023.05.15 2023.05.13 2023.05.19 2023.05.26 ..
+`date`event`incident`sym`price`recv_time`is_active!(2023.05.02 2023.05.14 2023.05.05 2023.05.06;..
+`date`event`incident`sym`price`recv_time`is_active!(2023.05.09 2023.05.12 2023.05.30 2023.05.10 ..
+`date`event`incident`sym`price`recv_time`is_active!(2023.05.04 2023.05.17 2023.05.05 2023.05.22;..