I have more than one matching rows on keyed column, so I'd like to show all the data What's the easiest/efficient way to achieve my final result please? Thank you However, ungroup will remove rows that ain't matched:
q)t1
col1 col2 col3
--------------
a 1 11
b 2 22
c 3 33
d 8 88
q)t2
col1 col4 col5
--------------
a 4 44
a 5 55
b 6 66
c 7 77
//ungroup removes the col1=d
q)t1 lj `col1 xgroup t2
col1 col2 col3 col4 col5
--------------------------------
a 1 11 4 5 44 55
b 2 22 ,6 ,66
c 3 33 ,7 ,77
d 8 88 `long$() `long$()
q)ungroup t1 lj `col1 xgroup t2
col1 col2 col3 col4 col5
------------------------
a 1 11 4 44
a 1 11 5 55
b 2 22 6 66
c 3 33 7 77
q)
//I want to achieve this
q)t:ungroup t1 lj `col1 xgroup t2
q)res:t upsert t1 except ?[t;();0b;{x!x} cols t1]
q)res
col1 col2 col3 col4 col5
a 1 11 4 44
a 1 11 5 55
b 2 22 6 66
c 3 33 7 77
d 8 88
//My 2nd question is that after I have the above res, I want to enrich res again but it fails with a type error:
q)t3:( col1:dd;col4:0 0;col5:0 00)
q)t3
col1 col4 col5
d 0 0
d 0 0
q)res lj col1 xgroup t3 'type [0] res lj col1 xgroup t3
^
q))<p>
You can use xkey to key the col1 column with lj to get your desired result:
t1 lj `col1 xkey t2
My joins cheatsheet might help you:https://www.linkedin.com/posts/michaela-woods-50502b60_kdb-dataanalytics-joinoperations-activity-7126225089632563200-jUOz?utm_source=share&utm_medium=member_desktop
As well as the joins modules in the free Academy courses: https://learninghub.kx.com/courses/kdb-developer-level-1/lessons/joins/
Michaela t2 has two rows with an "a" value as col1 so when you do the join like you did, it only joins with the last record. That's the catch with this question.
q)(t2 uj select distinct col1 from t1 where not col1 in t2`col1) lj `col1 xkey t1
col1 col4 col5 col2 col3
------------------------
a 4 44 1 11
a 5 55 1 11
b 6 66 2 22
c 7 77 3 33
d 8 88
Thanks for the clarification Alex, @kdb_newbie you can try the below:
ej[`col1;t1;t2] uj select from t1 where not col1 in t2.col1
col1 col2 col3 col4 col5
------------------------
a 1 11 4 44
a 1 11 5 55
b 2 22 6 66
c 3 33 7 77
d 8 88