topic Re: problem with Left Join in QlikView

problem with Left Join

markovalexsey — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: problem with Left Join

Digvijay_Singh — Mon, 19 Mar 2018 17:41:57 GMT

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.

Re: problem with Left Join

Digvijay_Singh — Mon, 19 Mar 2018 17:48:28 GMT

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;

Re: problem with Left Join

sasiparupudi1 — Mon, 19 Mar 2018 17:50:09 GMT

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,

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;

Re: problem with Left Join

markovalexsey — Tue, 20 Mar 2018 09:16:19 GMT

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

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

and it works.

Thanks, it works

Re: problem with Left Join

markovalexsey — Tue, 20 Mar 2018 09:19:50 GMT

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