Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Left join with distinct

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.

8 Replies

Re: Left join with distinct

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

Employee
Employee

Re: Left join with distinct

Same happened here, version 11.20SR10.

It´s really weird

Highlighted
Not applicable

Re: Left join with distinct

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.

Not applicable

Re: Left join with distinct

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.

prieper
Honored Contributor II

Re: Left join with distinct

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

MVP
MVP

Re: Left join with distinct

distinct is applied to resulting data set

google for

qlikview beware of distinct oleg naturalsynergies

and you'll find details and example

utkarshgarg
Contributor II

Re: Left join with distinct

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

Hi,

Also read the go for below link

http://www.qlikfix.com/2013/07/30/distinct-can-be-deceiving/

Regards

Regards,
Prashant Sangle
Community Browser