Add a filter based on other column values in kdb

Question

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

Answer 1

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

Answer 2

Give the same example table t and references ref from Alexanders example you can also build up a list of matching start and end sequences:

q)show m:{raze(s,\:"*";"*",/:s:string(),x)}ref
"USD*"
"EUR*"
"*USD"
"*EUR"

Then find the pairs that match those:

q)update max each pair like/:\:m from t
pair   m
--------
EURUSD 1
USDINR 1
BLRUSD 1
INRBLR 0
BLREUR 1

When also considering the time to build the list m and run the query this example is less efficient than above. If you only have to build the list m then this example may be more efficient, but it will depend on the size of the list ref.

Answer 3

Another alternative. Check for ref in each pair and OR over each pair of values in the resultant list

q)ref:`USD`EUR
q)update result:(|/')ref in/: `$0 3_/: string pair from t
pair   result
-------------
EURUSD 1
USDINR 1
BLRUSD 1
INRBLR 0
BLREUR 1