Joining 2 Fact tables on multiple keys based on Condition

[Anonymous]

Solution: (Conditional Join)

Link Table 1 (Key1) to Table 2 (Key1) if they exist

Else link Table 1 (Key2) to Table 2 (Key2)

Else "NULL".

How do i approach this solution, and build the linking key ?

Any help is appreciated.

Thanks,

[ahaahaaha]

Hi Abhay,

Can still a fragment of the original data and what do you want to get at the output?

Regards,

Andrey

[CarlosAMonroy]

Hi Abhay,

Maybe try something like this:

LinkTable:

Load if(len(Key1)>0,Key1,Key2) as Key, Key1 as T1.Key1, Key2 as T1.Key2, Field

From Table1;

join(LinkTable)

Load if(len(Key1)>0,Key1,Key2) as Key, Key1 as T2.Key1, Key2 as T2.Key2, Field

From Table2;

Hope that helps,

Carlos M

[aarkay29]

May be something like this :

A:

load * Inline [

A.ID1,A.ID2,Value

1,4,45

2,5,50

3,6,60

];

B:

load * Inline [

B.ID1,B.ID2,Product

1,,A

2,,B

3,5,C

,4,D,

1,4,G

];

Left Join (A)

Load 

  B.ID1 as A.ID1,

  Product

Resident

  B;

Join (A)

Load 

  B.ID2 as A.ID2,

  Product

Resident

  B

Where

  Not exists(A.ID1,B.ID1)

;

Drop Table B;