Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
sunny_talwar

Unorthodox (or conditional) Join

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

 

ID1ID2Value1Dim
10150XYZ
10260XYZ
10370XYZ
10480XYZ
10590XYZ
106100XYZ
107110XYZ
108120XYZ
109130XYZ
1010140XYZ
1011150XYZ
2010160XYZ
2011170ABC

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?

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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

];

View solution in original post

3 Replies
sunny_talwar
Author

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
MVP
MVP

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
Author

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