CODEWITHSUNDEEP
kdb+
Utsav
Utsav

Reputation: 5918

select all columns with suffix _test in q kdb

I have a partitioned table, similar to below table:

q)t:([]date:3#2019.01.01; a:1 2 3; a_test:2 3 4; b_test:3 4 5; c: 6 7 8);

date       a a_test b_test c
----------------------------
2019.01.01 1 2      3      6
2019.01.01 2 3      4      7
2019.01.01 3 4      5      8

Now, I want to fetch date column and all columns have names with suffix "_test" from table t.
Expected output:

date       a_test b_test
------------------------
2019.01.01 2      3
2019.01.01 3      4
2019.01.01 4      5

In my original table, there are more than 100 columns with name having _test so below is not a practical solution in this case.

q)select date, a_test, b_test from t where date=2019.01.01

I tried various options like below, but of no use:

q)delete all except date, *_test from select from t where date=2019.01.01

Upvotes: 2

Views: 1076

Answers (2)

Jamie Carton
Jamie Carton

Reputation: 71

A functional select is probably the best way to go here if you require adding further filters.

?[`t;();0b;{x!x}`date,exec c from meta t where c like "*_test"]

The functional form of any select quesry can be obtained by using the -5! operator on any SQL style statement. In the example below I have created a table with 20 fields, each one beginning with either a or b. I then use the functional form to define which fields I want.

q)tab:{[x] enlist x!count[x]#0}`$"_" sv ' raze string `a`b,/:\:til 10

q){[t;s]?[t;();0b;{[x] x!x} cols[t] where cols[t] like s]}[tab;"b*"]
b_0 b_1 b_2 b_3 b_4 b_5 b_6 b_7 b_8 b_9
---------------------------------------
0   0   0   0   0   0   0   0   0   0

q){[t;s]?[t;();0b;{[x] x!x} cols[t] where cols[t] like s]}[tab;"a*"]
a_0 a_1 a_2 a_3 a_4 a_5 a_6 a_7 a_8 a_9
---------------------------------------
0   0   0   0   0   0   0   0   0   0

q)-5!" select a,b from c"
?
`c
()
0b
`a`b!`a`b

Alternatively, if I don't require any filtering I can use the # operator as in below:

{[x;s] (cols[x] where cols[x]  like s)#x}[ tab;"a*"]

Upvotes: 2

Callum Biggs
Callum Biggs

Reputation: 1550

If the columns you are selecting are variable then you should use a functional qSQL statement to perform the query. The following can be used in your case

q)query:{[tab;dt;c]?[tab;enlist (=;`date;dt);0b;(`date,c)!`date,c]}
q)query[t;2019.01.01;cols[t] where cols[t] like "*_*"]
date       a_test b_test
------------------------
2019.01.01 2      3
2019.01.01 3      4
2019.01.01 4      5

In order to craft a particular functional statement, you can parse your query, putting dummy columns in place if you aren't sure what they should be

q)parse "select date,c1,c2 from tab where date=dt"
?
`tab
,,(=;`date;`dt)
0b
`date`c1`c2!`date`c1`c2

Upvotes: 3

Related Questions