cancel
Showing results for 
Search instead for 
Did you mean: 

aj outer join

sieber
New Contributor
Hi 
i want to join two tables on the their time column but i dont want to "left join" them as aj is doing but instead I want an outer join.

currently i am using a temporary table with the date values of both tables and doing 2 aj:

a:([]date:2014.01.01 2014.01.03 2014.01.05; a:1 2 3)
b:([]date:2014.01.01 2014.01.04 2014.01.05; b:4 5 6)
aj[`date;aj[`date;([]date:asc distinct a[`date],b`date);a];b]

is there a better version? especially the asc distinct is not quite performant i guess

Markus
5 REPLIES 5

LamHinYan
New Contributor
/ tables renamed to avoid symbol reuse
t1:([]date:2014.01.01 2014.01.03 2014.01.05; a:1 2 3);
t2:([]date:2014.01.01 2014.01.04 2014.01.05; b:4 5 6);
(1!t1) uj (1!t2)

©¬ date a b
2014.01.01 1 4
2014.01.03 2 0N
2014.01.05 3 6
2014.01.04 0N 5

unfortunately this is not the result I want. I want this:


q)a:([]date:2014.01.01 2014.01.03 2014.01.05; a:1 2 3)
b:([]date:2014.01.01 2014.01.04 2014.01.05; b:4 5 6)
q)aj[`date;aj[`date;([]date:asc distinct a[`date],b`date);a];b]q)
date       a b
--------------
2014.01.01 1 4
2014.01.03 2 4
2014.01.04 2 5
2014.01.05 3 6



i guess I can work with uj like this:
q)select date,fills a,fills b from xasc[`date] (1!t1) uj (1!t2)

but this takes twice the time than my aj approach with the temp table.

I think your aj approach is good

Unless you want the final result sorted, then you don’t need the asc

(aj only requires the “value” tables, a and b, to be sorted by date in your case)

aj[`date;aj[`date;([]date:distinct a[`date],b`date);a];b]


this looks nice, can be easily generalised to 2+ tables

q)t:{get x set flip(`date,x)!(asc n?2*n;n?n:10000000)}each`a`b`c;

q)\ts ([]date:asc distinct raze t@\:`date)aj[`date]/t
12118 805307968

the distinct (even with the asc) is less than 10% of time

q)\ts asc distinct raze t@\:`date
607 671088992

Cheers,
  Attila

LamHinYan
New Contributor
(uj/)1!'(Table1;Table2;Table3)
http://www.thalesians.com/finance/index.php/Knowledge_Base/Databases/Kdb#Union_join