Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Join Problem

Hi all,

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

I have two tables which look like that:

table1

IDPrice
121217,40
121315,30
121428,30

table2

IDGroup
1212Banana
1212Apple
1213Lemon

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!

1 Solution

Accepted Solutions
jvitantonio
Luminary Alumni
Luminary Alumni

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:

IDsuper groupdescription
1212Banana-AppleBananas and apples
1213Lemon-OrangesLemon

I hope this helps.

J

View solution in original post

4 Replies
swuehl
MVP
MVP

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?

Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand
jvitantonio
Luminary Alumni
Luminary Alumni

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:

IDsuper groupdescription
1212Banana-AppleBananas and apples
1213Lemon-OrangesLemon

I hope this helps.

J

Anonymous
Not applicable
Author

Thank you very much for your answers.

The "left keep" didn't work!