aj outer join

Community Support
1

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],bdate);a];b]

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

Markus

2

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

©¬ dateab

2014.01.0114

2014.01.0320N

2014.01.0536

2014.01.040N5

3

(uj/)1!'(Table1;Table2;Table3)
http://www.thalesians.com/finance/index.php/Knowledge\_Base/Databases/Kdb#Union\_join

4

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],bdate);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.

5

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],bdate);a];b]

6

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)}eachabc;

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