-
ungroup, xgroup, lj
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 <br></pre><pre>//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:`d`d;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))\-
This discussion was modified 1 day, 9 hours ago by
kdb_newbie.
-
This discussion was modified 1 day, 9 hours ago by
-
6 Replies
-
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/
linkedin.com
Michaela Woods on LinkedIn: #kdb #dataanalytics #joinoperations #cheatsheet
Joining tables in kdb+/q is straightforward, but selecting the correct type of join can be tricky, especially for newcomers. 🧐 I've put together a quick…
-
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.
-
-
If you pad out t2 ensuring it has a row for every col1 value then you’ll get what you want:
q)t1:([] col1:`a`b`c`d;col2:1 2 3 8;col3:11 22 33 88) q)t2:([] col1:`a`a`b`c;col4:4 5 6 7;col5:44 55 66 77)
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
-
This reply was modified 19 hours, 31 minutes ago by rocuinneagain.
-
This reply was modified 19 hours, 30 minutes ago by rocuinneagain.
-
This reply was modified 19 hours, 27 minutes ago by rocuinneagain.
-
This reply was modified 19 hours, 31 minutes ago by
-
My take:
q)t1:([]col1:`a`b`c`d;col2:1 2 3 8;col3:11 22 33 88)
q)t2:([]col1:`a`a`b`c;col4:4 5 6 7;col5:44 55 66 77)
q)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 -
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
-
My first idea was the ej but I just didn’t like the fact that I had to add the missing rows from t1
Log in to reply.