I have the below test table:
data:([]isin:`abc`;cusip:``jkl;sedol:`mno`)
I need to update data with instrumentID and instrumentIDType columns with the following priority: isin,cusip,sedol.
If isin is null we use cusip, if cusip is null we use sedol, but I also need to update the corresponding instrumentIDType that I have used. Is there a better way to do it than what's below?
data:update instrumentID:isin, instrumentIDType:`ISIN from data;
data:update instrumentID:cusip, instrumentIDType:`CUSIP from data where null isin;
data:update instrumentID:sedol, instrumentIDType:`SEDOL from data where null instrumentID;
data:update instrumentID:ticker, instrumentIDType:`TICKER from data where null instrumentID;
Using fill (^) for instrumentID and a vector conditional (?) for instrumentIDType:
q)update instrumentID:sedol^cusip^isin,instrumentIDType:?[null isin;?[null cusip;?[null sedol;`TICKER;`SEDOL];`CUSIP];`ISIN]from data
isin cusip sedol instrumentID instrumentIDType
----------------------------------------------
abc mno abc ISIN
jkl jkl CUSIP