topic Re: JOIN on null value in QlikView

JOIN on null value

Thu, 11 Feb 2016 16:25:22 GMT

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

LOAD * INLINE [

A, B, C

1, 2

11,22,33

];

join

LOAD * INLINE [

A, B, C, D

1, 2, 3, 4

];

Result:

A	B	C	D
1	2
1	2	3	4
11	22	33

Expected:

A	B	C	D
1	2	3	4
11	22	33

Re: JOIN on null value

Gysbert_Wassenaar — Thu, 11 Feb 2016 17:15:43 GMT

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;

Re: JOIN on null value

sunny_talwar — Thu, 11 Feb 2016 18:47:05 GMT

You can also try this:

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,

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

Resident A;

DROP Table A;

Re: JOIN on null value

Wed, 17 Feb 2016 18:46:37 GMT

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