Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

robert2012
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
Not applicable

Re: Join Problem

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

4 Replies
swuehl
Not applicable

Re: Join Problem

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
Not applicable

Re: Join Problem

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
Not applicable

Re: Join Problem

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

robert2012
Not applicable

Re: Join Problem

Thank you very much for your answers.

The "left keep" didn't work!