Joining/merging with unkeyed tables

Community Support
1

Hey guys, 1 month new to q/kdb+. Hoping someone can help me out. Ill keep it simple:

Table 1:- has Alot of rows, need all of them
A | B
1 | 10
2 | 20
3 | 30

Table 2:- small number of rows but has multiple entries for each
A | C
1 | 100
2 | 200
2 | 300

What i want as output:
A | B | C
1 | 10 | 100
2 | 20 | 200
2 | 20 | 300
3 | 30 |

In simple words:
table1 lj table2 when table2 is unkeyed. so want to add table1 rows multiple times for each matching row in table2. Finally need all rows of table1.

What all I tried:

  • LJ:- t2 lj t1 could’ve worked but wont give me the extra rows of table1

  • UJ:- needs both to be keyed or both unkeyed. Both unkeyed gives this-
    A | B | C
    1 | 10 |
    2 | 20 |
    3 | 30 |
    1 | | 100
    2 | | 200
    2 | | 300

    Its a simple kind of join but seems to require some preprocessing with ungroup and groupby or something. Was wondering if there’s a simple way to do this.

3 Answers

3
2

You want to use an equi-join ej
ej – equi join | Reference | kdb+ and q documentation - kdb+ and q documentation

ej[`a;t;t2]
a b c

Yes, EJ gives the matched rows multiple times for multiple entries in table2. But it doesn't give the unmatched rows of table1. Best solution I could think of is concatenating- 1. EJ 2. the unmatched rows from table1 using UJ LIKE THIS: t3:0!select B,C by A (t1 uj t2); ANS: ej[`a;t1;t2] , ungroup select from t3 where C ~\: enlist 0N Is this really the simplest way?

 –
3

Yes, EJ gives the matched rows multiple times for multiple entries in table2. But it doesn't give the unmatched rows of table1.

Best solution I could think of is concatenating-

  1. EJ
  2. the unmatched rows from table1 using UJ

LIKE THIS:

t3:0!select B,C by A (t1 uj t2);
ANS: ej[`a;t1;t2] , ungroup select from t3 where C ~\: enlist 0N

Is this really the simplest way?

4

Including q code so others can reproduce:

q)t1:([] A:1 2 3;B:10 20 30)
q)t1
A B
----
1 10
2 20
3 30

q)t2:([] A:1 2 2;C:100 200 300)
q)t2
A C
-----
1 100
2 200
2 300

Two approaches to a solution:

q)ungroup update {$[0~count x;1#x;x]} each C from (`A xkey t1) uj select C by A from t2
A B  C
--------
1 10 100
2 20 200
2 20 300
3 30

q)ungroup (`A xkey select C:count[i]#enlist 0N by A,B from t1),select C by A from t2
A B  C
--------
1 10 100
2 20 200
2 20 300
3 30