topic Re: Join Problem in QlikView

Join Problem

Anonymous — Mon, 09 Sep 2013 14:05:12 GMT

Hi all,

I have a question to the topic: "Join".

I have two tables which look like that:

table1

ID	Price
1212	17,40
1213	15,30
1214	28,30

table2

ID	Group
1212	Banana
1212	Apple
1213	Lemon

Now I joined table2 with "left join" into table1.

My problem is, that after the joining the ID 1212 will be doubled as there are two groups (Banana and Apple) possible. Now if I sum the prices of the different IDs, the result of ID 1212 is wrong cause of the problem.(17,40 +17,40)

Do you have an idea how to solve this problem? Can I use another join, so that e.g. Apple is not joined and only banana 🙂 ??

If you need further information, just let me know!

Thank you for helping!

Re: Join Problem

swuehl — Mon, 09 Sep 2013 14:11:41 GMT

Do you have an idea how to solve this problem? Can I use another join, so that e.g. Apple is not joined and only banana 🙂 ??

Sure, you can probably create a modified table2 with a distinct ID, where you have to decide which Group value to use.

But have you tried, not joining the tables, instead keep them just linked / associated?

Re: Join Problem

Gysbert_Wassenaar — Mon, 09 Sep 2013 14:13:57 GMT

You can use left keep instead of left join

You can try using a different aggregation function. For example avg(Price) instead of sum(Price)

Or you can try something like this:

Table:

Load ID, Price

from source1;

left join

Load ID, firstvalue(Group) as Group

from source2

group by ID;

Re: Join Problem

jvitantonio — Mon, 09 Sep 2013 14:17:33 GMT

Hello,

You should create a unique key in table 2 and use that in table 1. Another idea is to create a super group and add the column "group" as a description:

ID	super group	description
1212	Banana-Apple	Bananas and apples
1213	Lemon-Oranges	Lemon

I hope this helps.

Re: Join Problem

Anonymous — Mon, 09 Sep 2013 14:39:22 GMT

Thank you very much for your answers.

The "left keep" didn't work!