In KDB, is it possible to perform a lj (Left-Join) using "like" or "~" to join 2 tables where 1 table's key matches another tables's key by regex?
1 Answers
Not using out-of-the-box tools, but you could do something like this (won't be incredibly efficient)
q)t:([]sym:`ACF`ABC`ABD`BA`AAF`AABG`CDE;col1:til 7)
q)t2:([]regex:("*AB*";"AA?";"A*";"C*");col2:4#.Q.A)
q)t,'t2 first each where each t[`sym]like'\:t2[`regex]
sym col1 regex col2
---------------------
ACF 0 "A*" C
ABC 1 "*AB*" A
ABD 2 "*AB*" A
BA 3 ""
AAF 4 "AA?" B
AABG 5 "*AB*" A
CDE 6 "C*" D
This approach would take the first matched pattern if there's more than one match.
Another idea is to create a manufactured key and left join on the manufactured key.
Related