Search code examples
databasekdbq-lang

Add a filter based on other column values in kdb

I have a column containing symbols like EURUSD, USDINR etc. I need to create a new column based on the condition that if any of the first three or last three alphabets of the currency pairs belongs to this list
ref: ("INR", "BLR"....)
then I have to create a new column "result" and update it as "yes" if condition is satisfied otherwise "No".

Can anyone please help me with this. Thanks

Solution

  • Given

    q)show t:([]pair:`EURUSD`USDINR`BLRUSD`INRBLR`BLREUR)
pair
------
EURUSD
USDINR
BLRUSD
INRBLR
BLREUR

    and

    q)ref:`USD`EUR

    the straightforward query to add the described "result" column would be

    q)update result:((`$3_'string pair)in\:ref)or(`$3#'string pair)in\:ref from t
pair   result
-------------
EURUSD 1
USDINR 1
BLRUSD 1
INRBLR 0
BLREUR 1

    but the following alternative will probably be faster:

    q)update result:0<count each ref inter/:`$0 3_/:string pair from t
pair   result
-------------
EURUSD 1
USDINR 1
BLRUSD 1
INRBLR 0
BLREUR 1