Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
igorgois_
Partner - Creator
Partner - Creator

Left join with distinct

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

1 Solution

Accepted Solutions
agilos_mla
Partner - Creator III
Partner - Creator III

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 

View solution in original post

4 Replies
tchovanec
Creator II
Creator II

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_
Partner - Creator
Partner - Creator
Author

Hello,

I was expecting 4 rows instead of 3.

I do not understand why

agilos_mla
Partner - Creator III
Partner - Creator III

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_
Partner - Creator
Partner - Creator
Author

Add a rowno() column is genius!

 

thank you