Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

JOIN on null value

Hi I have a script like below,  how do I get to the expected result? Tried join but didn't work. Thank you!

A:

LOAD * INLINE [

    A, B, C

    1, 2

    11,22,33

];

join

LOAD * INLINE [

    A, B, C, D

    1, 2, 3, 4

];


Result:

ABCD
12
1234
112233


Expected:

ABCD
1234
112233
1 Solution

Accepted Solutions
Gysbert_Wassenaar

So you want to join a 3 with a null. That's not going to happen. 3 will never be null.

Try joining without C:

Temp:

LOAD * INLINE [

    A, B, C

    1, 2

    11,22,33

];

join

LOAD A, B, C as X, D INLINE [

    A, B, C, D

    1, 2, 3, 4

];

Result

LOAD A, B, If(Len(Trim(C))=0,X,C) as C, D

RESIDENT Temp;

DROP TABLE Temp;


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
Gysbert_Wassenaar

So you want to join a 3 with a null. That's not going to happen. 3 will never be null.

Try joining without C:

Temp:

LOAD * INLINE [

    A, B, C

    1, 2

    11,22,33

];

join

LOAD A, B, C as X, D INLINE [

    A, B, C, D

    1, 2, 3, 4

];

Result

LOAD A, B, If(Len(Trim(C))=0,X,C) as C, D

RESIDENT Temp;

DROP TABLE Temp;


talk is cheap, supply exceeds demand
sunny_talwar

You can also try this:

A:

LOAD A,

  B, C as E;

LOAD * INLINE [

    A, B, C

    1, 2

    11,22,33

];

join

LOAD * INLINE [

    A, B, C, D

    1, 2, 3, 4

];

Final:

LOAD A,

  B,

  If(Len(Trim(C)) = 0, E, C) as C,

  D

Resident A;

DROP Table A;


Capture.PNG

Not applicable
Author

Thank you both! Both are correct, too bad the system only allowed me to mark one...