Left Join in Qlik

[Priyanka1902]

Hi,

I have 2 tables:

Project Number	Hours
A745	100
B675	200

Project Number	Sub Project Number
A745	A745001
A745	A745002
B675	B675001

I am using Left Join to join table 2 with Table 1. After reload when I do the Sum(Hours), it shows values 400 bcz it is adding 100, 2 times. is there any way where I will get 300 which is correct. Please help.

[TauseefKhan]

Hi @Priyanka1902,

Use: Sum(distinct(Hours))

[hic]

Don't join. If you keep it in two tables you will have a correct data model, and the Qlik engine will do what you want.

[Vegar]

As HIC is saying, don't join. Keep the two tables as they are. The data will be associated with each other through their common field name, but not duplicated as you experience with the join.

The reason you get 400 when joining is that your join on Project number will  make both the Sub Project Numbers of A745 get the 100 hours value on their row. Therfore S745 will sum up to 200 hours and not 100 hours. This problem will be avoided with HICs suggestion.

[rwunderlich]

I agree with @hic suggestion. I might also ask why there are duplicate rows in Table 2? Are they needed? It might be appropriate to add the DISTINCT keyword when loading Table 2 to avoid the duplicates. 

-Rob