Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Master III
Master III

Re: problem with Left Join

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
Highlighted
Master III
Master III

Re: problem with Left Join

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.

Highlighted
Master III
Master III

Re: problem with Left Join

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;

Highlighted
Master III
Master III

Re: problem with Left Join

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

Highlighted
Contributor III
Contributor III

Re: problem with Left Join

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

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

and it works.

Thanks, it works

Highlighted
Contributor III
Contributor III

Re: problem with Left Join

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