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

Question

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};

Thomas Smyth · Accepted Answer · 2019-05-21 08:51:08Z

6

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

edited May 21, 2019 at 8:51

answered May 20, 2019 at 14:11

Thomas Smyth

5,6546 gold badges27 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jorge Sawyer · Accepted Answer · 2019-05-20 14:14:23Z

2

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

answered May 20, 2019 at 14:14

Jorge Sawyer

1,3416 silver badges7 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related