Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Good eye brother. I would be curious to know why the two spits out different results.
Same happened here, version 11.20SR10.
It´s really weird
Hi Anosh,
When you use disting with join or concatenate in QlikView it works for all tables which are joined or concatenated. Istead of this In SQL it work only for table where you use it.
In order to prove it you can try to do concatenate in qlikview using disting for one table. You will see that it apply for all of them.
Your second example
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;
Disting apply only for one table because tab2 is loaded without connection (join or concatenate) to any other table.
This is what I know from my own experience.
Thanks Taras for your response but I don't understand any benefit of doing distinct after joining the table. As Qlikview process data sequentially, its hard to digest this.
Think it follows a bit the logic that you should not have duplicated records in your table and might be related to the inner architecture of Qlik ...
Would anyhow recommend to rather use MAPPING than JOIN - results are more predictable and you may already anticipate for possible mismatches.
Peter
distinct is applied to resulting data set
google for
qlikview beware of distinct oleg naturalsynergies
and you'll find details and example
Distinct is applied on the resulting set. Have a look at this link:
Hi,
Also read the go for below link
http://www.qlikfix.com/2013/07/30/distinct-can-be-deceiving/
Regards