# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
cancel
Showing results for
Did you mean:
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:

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:

F1, F2, F5

q1, w1, r11

q2, w2, r22

q3, w3, r33

q4, w4, r44

q5,  , r55

];

Left Join(test_1)

;

DROP Table test_2;

but as a result, I lose,   q5+r55

desired result:

test_1:

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

Try using applymap

test_1:

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:

Distinct

F1,F2

Resident test_1;

NoConcatenate

test_2:

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)

;

DROP Table test_2;

5 Replies

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.

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

NoConcatenate

Final:

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;

Master III

Try using applymap

test_1:

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:

Distinct

F1,F2

Resident test_1;

NoConcatenate

test_2:

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)

;

DROP Table test_2;

Contributor III
Author