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
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
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.
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
