cancel
Showing results for 
Search instead for 
Did you mean: 

Special join

michel_berne201
New Contributor
Hi All , 


If someone could help on this one, it would be great !

I have:


t:([] a: 1 2 3;b:("abc";"dfg";"hhj");c:("ert";"ghr";"ghh"))

u:([]a: til 5;b:("abc";"vbn";"dfg";"lkj";"poi"))


I would like to left join u and t but if value is in t I want it replaced by t.c

result:

a b 
-----
0 "ert"
1 "vbn"
2 "ghr"
3 "lkj"
4 "poi"

Thx
2 REPLIES 2

rathore_ajay
New Contributor
q)update b:count[i]#value((b!b),exec b!c from t) from u
a b
-------
0 "ert"
1 "vbn"
2 "ghr"
3 "lkj"
4 "poi"



-Ajay

sanderroomuscwo
New Contributor
Dear Michel,

The following function will do what you asked:

q)update (t[`b]!t`c)b from u where b in t`b
a b
-------
0 "ert"
1 "vbn"
2 "ghr"
3 "lkj"
4 "poi"



The dictionary inside the update statement between b and c columns in t to replace the b values in u where appropriate.


q
)t[`b]!t[`c]
"abc"| "ert"
"dfg"| "ghr"
"hhj"| "ghh"




Hope this helps!