kdb union join (with plus join)

user_14096278 0
Viewed 171

I have been stuck on this for a while now, but cannot come up with a solution, any help would be appriciated

I have 2 table like

q)x
a b c d
--------
1 x 10 1
2 y 20 1
3 z 30 1

q)y
a b| c d
---| ----
1 x| 1 10
3 h| 2 20

Would like to sum the common columns and append the new ones. Expected result should be

a b c d
--------
1 x 11 11
2 y 20 1
3 z 30 1
3 h 2  20

pj looks to only update the (1,x) but doesn't insert the new (3,h). I am assuming there has to be a way to do some sort of union+plus join in kdb

kdb
2 Answers

You can take advantage of the plus (+) operator here by simply keying x and adding the table y to get the desired table:

q)(2!x)+y
a b| c  d
---| -----
1 x| 11 11
2 y| 20 1
3 z| 30 1
3 h| 2  20

The same "plus if there's a matching key, insert if not" behaviour works for dictionaries too:

q)(`a`b!1 2)+`a`c!10 30
a| 11
b| 2
c| 30
user_142540500

got it :)

q) (x pj y), 0!select from y where not ([]a;b) in key 2!x
a b c d
--------
1 x 11 11
2 y 20 1
3 z 30 1
3 h 2  20

Always open for a better implementation :D I am sure there is one.

user_140962780
Related