Left join with distinct

[igorgois_]

When I use the left join  with distinct, it applies in the fact table and not in the category inline load.

It turns out it perfoms the join operation first and then the distinct.

Does anyone know why?

fact:
load * Inline [
product,qty
a,1
b,2
c,3
b,2
];

left join
load distinct * inline
[
product,category
a,dress

b,shirt

c,tennis

b,shirt
];

Thanks in advance

[tchovanec]

I am unsure of the output you expect. This code will give you 3 rows.

fact:
load * Inline [
product,qty
a,1
b,2
c,3
b,2
];

test:
load * inline
[
product,category
a,dress

b,shirt

c,tennis

b,shirt
];

left join(fact)
load *
resident test;

drop table test;

exit script;

Also, if you change the code to be the following it will also give 3 rows. That is just the behavior of a left join.

left join(fact)
load distinct *
resident test;

Let me know if I am not understanding something.

[igorgois_]

Hello,

I was expecting 4 rows instead of 3.

I do not understand why

[agilos_mla · Accepted solution]

Hi

this is the Norma behavior of Qlik , as soon of you use the distinct keyword in a statement it is applied in all the tables involved in a join or concatenate. 
Can be solve using a rowno() in your first table to have unique ID or in two steps with the category table, first load distinct then left join. I know can be painful but works like that 😉

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

all the best

MICHAEL 

[igorgois_]

Add a rowno() column is genius!

thank you