Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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:
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;
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;
instead of ApplyMap('Map_F2',F2,F2) AS F2,
used ApplyMap('Map_F2',F1, F2) AS F2,
and it works.
Thanks, it works
very Thanks, your method works, but ApplyMap in my model like better