topic Re: Left join with distinct in QlikView

Left join with distinct

Fri, 20 Mar 2015 20:14:24 GMT

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

];

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

];

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

];

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.

Re: Left join with distinct

sunny_talwar — Fri, 20 Mar 2015 20:30:58 GMT

Good eye brother. I would be curious to know why the two spits out different results.

Re: Left join with distinct

Clever_Anjos — Fri, 20 Mar 2015 20:32:56 GMT

Same happened here, version 11.20SR10.

It´s really weird

Re: Left join with distinct

Fri, 20 Mar 2015 20:38:21 GMT

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

];

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.

Re: Left join with distinct

Fri, 20 Mar 2015 21:22:24 GMT

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.

Re: Left join with distinct

prieper — Sat, 21 Mar 2015 09:18:04 GMT

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

Re: Left join with distinct

maxgro — Sat, 21 Mar 2015 10:45:19 GMT

distinct is applied to resulting data set

google for

qlikview beware of distinct oleg naturalsynergies

and you'll find details and example

Re: Left join with distinct

Anonymous — Sat, 21 Mar 2015 12:02:46 GMT

Distinct is applied on the resulting set. Have a look at this link:

QlikView Blog Q-Tip #1 - Beware of DISTINCT!

Re: Left join with distinct

PrashantSangle — Sat, 21 Mar 2015 12:31:37 GMT

Hi,