Unorthodox (or conditional) Join

[sunny_talwar]

Hey Team -

I feel like I might have asked a similar question in the past, but was not able to find it. Here is a sample

Table1:

LOAD ID1,

  If(Len(Trim(ID2)) > 0, ID2) as ID2,

  Dim;

LOAD * INLINE [

    ID1, ID2, Dim

    10, , XYZ

    20, 10, XYZ

    20, 11, ABC

];

Table2:

LOAD * INLINE [

    ID1, ID2, Value1

    10, 1, 50

    10, 2, 60

    10, 3, 70

    10, 4, 80

    10, 5, 90

    10, 6, 100

    10, 7, 110

    10, 8, 120

    10, 9, 130

    10, 10, 140

    10, 11, 150

    20, 10, 160

    20, 11, 170

];

and this is the output I am hoping to get

ID1	ID2	Value1	Dim
10	1	50	XYZ
10	2	60	XYZ
10	3	70	XYZ
10	4	80	XYZ
10	5	90	XYZ
10	6	100	XYZ
10	7	110	XYZ
10	8	120	XYZ
10	9	130	XYZ
10	10	140	XYZ
10	11	150	XYZ
20	10	160	XYZ
20	11	170	ABC

Essentially the idea is that join on just ID1 when ID2 in table1 is null. Else, join on ID1 and ID2. What would be the best way to do this?

[sunny_talwar]

Here is one way, but is there anything better?

Table1:

LOAD ID1,

  If(Len(Trim(ID2)) > 0, ID2) as ID2,

  Dim;

LOAD * INLINE [

    ID1, ID2, Dim

    10, , XYZ

    20, 10, XYZ

    20, 11, ABC

];

Table2:

LOAD * INLINE [

    ID1, ID2, Value1

    10, 1, 50

    10, 2, 60

    10, 3, 70

    10, 4, 80

    10, 5, 90

    10, 6, 100

    10, 7, 110

    10, 8, 120

    10, 9, 130

    10, 10, 140

    10, 11, 150

    20, 10, 160

    20, 11, 170

];

Left Join (Table2)

LOAD ID1,

  ID2,

  Dim as Dim1

Resident Table1

Where not IsNull(ID2);

Left Join (Table2)

LOAD ID1,

  Dim as Dim2

Resident Table1

Where IsNull(ID2);

DROP Table Table1;

FinalTable:

LOAD ID1,

  ID2,

  If(Len(Trim(Dim1)) = 0, Dim2, Dim1) as Dim,

  Value1

Resident Table2;

DROP Table Table2;

[MK_QSL · Accepted solution]

Table1:

Mapping LOAD

  ID1 & ID2 as Key,

  Dim

;

LOAD * INLINE [

    ID1, ID2, Dim

    10, , XYZ

    20, 10, XYZ

    20, 11, ABC

];

Table2:

Mapping LOAD

  ID1 as Key2,

  Dim

Where Len(TRIM(ID2))=0;

LOAD * INLINE [

    ID1, ID2, Dim

    10, , XYZ

    20, 10, XYZ

    20, 11, ABC

];

Table2:

LOAD

  ID1,

  ID2,

  ID1 & ID2,

  ApplyMap('Table1',ID1&ID2, ApplyMap('Table2',ID1,Null())) as Dim,

Value1

INLINE

[

    ID1, ID2, Value1

    10, 1, 50

    10, 2, 60

    10, 3, 70

    10, 4, 80

    10, 5, 90

    10, 6, 100

    10, 7, 110

    10, 8, 120

    10, 9, 130

    10, 10, 140

    10, 11, 150

    20, 10, 160

    20, 11, 170

];

[sunny_talwar]

This is the same one I came up with. I just need to check if I can replicate this in my original application or not.

Thanks,

Sunny