Hi,
I would like to understand the different between Left Join and Left Join with distinct keyword with the below script.
With distinct keyword:
tab1:
LOAD * INLINE [
id, value
1, a
2, b
3, c
3, c
];
left join(tab1)
LOAD distinct * INLINE [
id, field
1, aa
2, bb
3, cc
];
without distinct keyword:
tab1:
LOAD * INLINE [
id, value
1, a
2, b
3, c
3, c
];
left join(tab1)
LOAD * INLINE [
id, field
1, aa
2, bb
3, cc
];
Results are different here. The final table has only three records with distinct but 4 without distinct.
My understanding is that, 2nd inline load should distinctly load data and then join with tab1, but it is not doing it instead it is taking distinct on the final resulted table.
If i write the script as:
tab1:
LOAD * INLINE [
id, value
1, a
2, b
3, c
3, c
];
tab2:
NoConcatenate
LOAD distinct * INLINE [
id, field
1, aa
2, bb
3, cc
];
left join(tab1) load * Resident tab2;
drop table tab2;
then the final table will have 4 records.
Please help me understand the execution difference of left join with distinct in Qlikview and SQL.
