ungroup, xgroup, lj

Community Support
,
1

https://learninghub.kx.com/forums/topic/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


//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>

2

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/

3

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. 

4

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



5

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

6

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

7

My first idea was the ej but I just didn't like the fact that I had to add the missing rows from t1

8

thank you all!!!