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: 
markovalexsey
Contributor III
Contributor III

problem with Left Join

Good afternoon,

help solve the problem with Left Join

Source tables:

Set NullInterpret = ''; //attempt to replace null() in a cell

test_1:

LOAD * INLINE [

    F1, F2, F3, F4

    q1, w1, 23, 67

    q2, w2, 34, 78

    q3, w3, 45, 89

    q4, w4, 56, 90

    q5, w5, 67, 12   

];

NoConcatenate

test_2:

LOAD * INLINE [

    F1, F2, F5

    q1, w1, r11

    q2, w2, r22

    q3, w3, r33

    q4, w4, r44

    q5,  , r55

];

Left Join(test_1)

LOAD * Resident test_2

;

DROP Table test_2;

but as a result, I lose,   q5+r55


desired result:

test_1:

LOAD * INLINE [

    F1, F2, F3, F4, F5

    q1, w1, 23, 67, r11

    q2, w2, 34, 78, r22

    q3, w3, 45, 89, r33

    q4, w4, 56, 90, r44

    q5, w5, 67, 12, r55   

];

I will be grateful for the help

1 Solution

Accepted Solutions
sasiparupudi1
Master III
Master III

Try using applymap

test_1:


LOAD * INLINE [


    F1, F2, F3, F4


    q1, w1, 23, 67


    q2, w2, 34, 78


    q3, w3, 45, 89


    q4, w4, 56, 90


    q5, w5, 67, 12  


];


Map_F2:

Mapping Load

Distinct

F1,F2

Resident test_1;



NoConcatenate


test_2:


LOAD

F1,

ApplyMap('Map_F2',F2,F2) AS F2,

F5

INLINE [


    F1, F2,F5


    q1, w1,r11


    q2, w2,r22


    q3, w3,r33


    q4, w4,r44


    q5,,r55


];



Left Join(test_1)


LOAD * Resident test_2


;


DROP Table test_2;

View solution in original post

5 Replies
Digvijay_Singh

You are missing Q5 + R5 because in left join it discards all the rows of right table where there is no match. You need both q5 and w5 in F1 and F2 to be able to include that row in the final table.

If you use JOIN instead of Left Join, you will have all the rows from left and right table but then it won't show F5 in the same row of q5 as common columns don't have same values.

Digvijay_Singh

you may add this at the end to achieve desired results -

NoConcatenate


Final:

Load

F1,

Concat(F2) as F2,

Concat(F3) as F3,

Concat(F4) as F4,

Concat(F5) as F5

Resident test_1

Group By F1;

drop Table test_1;

sasiparupudi1
Master III
Master III

Try using applymap

test_1:


LOAD * INLINE [


    F1, F2, F3, F4


    q1, w1, 23, 67


    q2, w2, 34, 78


    q3, w3, 45, 89


    q4, w4, 56, 90


    q5, w5, 67, 12  


];


Map_F2:

Mapping Load

Distinct

F1,F2

Resident test_1;



NoConcatenate


test_2:


LOAD

F1,

ApplyMap('Map_F2',F2,F2) AS F2,

F5

INLINE [


    F1, F2,F5


    q1, w1,r11


    q2, w2,r22


    q3, w3,r33


    q4, w4,r44


    q5,,r55


];



Left Join(test_1)


LOAD * Resident test_2


;


DROP Table test_2;

markovalexsey
Contributor III
Contributor III
Author

instead of  ApplyMap('Map_F2',F2,F2) AS F2,

used          ApplyMap('Map_F2',F1, F2) AS F2,

and it works.

Thanks, it works

markovalexsey
Contributor III
Contributor III
Author

very Thanks, your method works, but ApplyMap in my model like better