JOIN on null value

[#1]

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:

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

[Gysbert_Wassenaar · Accepted solution]

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;

[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;

[#4]

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