CODEWITHSUNDEEP
kdb+
Utsav
Utsav

Reputation: 5918

In select query, set column value, based on if -else if condition in q kdb

We have a table t:

t:([] sym:`GOOG`IBM`APPL; px:10 20 30; size:1000 2000 3000)

Now we want to select assign a column in output based on a condition provided in a function.

{[m]select sym, px, size, eb:?[`ab=m;`cd;`ef] from t where size>1000}[`ab] / This works fine providing proper value to eb in output(if/else)

But my requirement is based on (if/else if) to set the value of eb as below, tried ?, $ but didn't work

{[m]select sym, px, size, eb:?[`ab=m;`cd;`yz=m;`ef] from t where size>1000}[`ab] / It fails with type error

requirement(Sudo code):

if (m==ab) { return cd};
else if (m==yz) {return ef};

Upvotes: 1

Views: 4233

Answers (2)

Thomas Smyth
Thomas Smyth

Reputation: 5644

When using vector conditional ? you need to nest the conditionals. In this example not matching either condition will return a null.

q){[m]select sym, px, size, eb:?[`ab=m;`cd;?[`yz=m;`ef;`]] from t where size>1000}[`ab]
sym  px size eb
---------------
IBM  20 2000 cd
APPL 30 3000 cd

If you have a lot of discrete conditions this can become very unwieldy, an alternative would be to use a dictionary.

q)dict:`ab`yz!`cd`ef
q){[m]select sym, px, size, eb:dict[m] from t where size>1000}[`ab]
sym  px size eb
---------------
IBM  20 2000 cd
APPL 30 3000 cd

Upvotes: 6

Jorge Sawyer
Jorge Sawyer

Reputation: 1341

You can also use the find [?] operator to define a function to build that column:

q)f:{`cd`ef` `ab`yz?x}
q)f[`ab]
`cd
q)f[`yz]
`ef
q)f[`jk] // no match
`
q)select sym, px, size, eb:f[`ab] from t where size>1000
sym  px size eb
---------------
IBM  20 2000 cd
APPL 30 3000 cd

Upvotes: 2

Related Questions