Case When statement for KDB

user_7633542 0
Viewed 517

Familiar with sql statements a bit more - have this column table VENUE. The column contains strings such as NYSE, NASDAQ, CBOE, and more. I want to create a new column where I define it based of the VENUE values.

so in SQL I would state -

select VENUE, 
Case when VENUE = 'NYSE' then 'Primary'
when VENUE <> 'NYSE then 'NotPrimary'
else VENUE end as VenueType 
from data

How can i achieve this in kdb? any guidance is much appreciated.

kdb sql
3 Answers

I am not familiar with SQL syntax but it looks like for your case you should be able to use vector conditional operator like so:

 q)show t:([]VENUE:`NYSE`NYSE`ASX`NZX`TKO)
VENUE
-----
NYSE
NYSE
ASX
NZX
TKO
q)update VENUETYPE:?[`NYSE=VENUE;`Primary;`NotPrimary] from t
VENUE VENUETYPE
----------------
NYSE  Primary
NYSE  Primary
ASX   NotPrimary
NZX   NotPrimary
TKO   NotPrimary
user_89124980

As Michael pointed out, the vector conditional will work when you have a small number of resulting values (primary/notPrimary). In the more general case where you could have many resulting values then you might be better using a dictionary to remap the values.

d:`NYSE`ASX`NZX`TKO!`case1`case2`case2`case3;
q)update VenueType:d[VENUE] from t
VENUE VenueType
---------------
NYSE  case1
NYSE  case1
ASX   case2
NZX   case2
TKO   case3

You can leave unmapped lookups blank

q)d:`NYSE`ASX!2#`primary
q)update VenueType:d[VENUE] from t
VENUE VenueType
---------------
NYSE  primary
NYSE  primary
ASX   primary
NZX
TKO

or fill them with a default value

q)update VenueType:`notPrimary^d[VENUE] from t
VENUE VenueType
----------------
NYSE  primary
NYSE  primary
ASX   primary
NZX   notPrimary
TKO   notPrimary
user_38956970

You can nest the conditionals as well if need be, i.e.:

select VENUE, 
    type:
        ?[VENUE = `NYSE; `$"New York";
        ?[VENUE = `NSDQ; `Nasdaq;
            `Other]]
from data
user_93996880
Related