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: 
Rehan
Creator III
Creator III

Left Join (1 to Many Relationship)

I have  the below script

Table A

FIeld A

Left Join (Table A)

Field A 

Field B

Field B can have multiple values for each value in Field B

When I am doing a table with Sum(Sales) and add Field B to the table the Sales amount will be doubled or tripled as the case may be.

How can I correctly show Field B without duplicating the sum sales

4 Replies
Rehan
Creator III
Creator III
Author

Ignore the other one . Please use this one

I have  the below script

Table A

FIeld A

Left Join (Table A)

Field A 

Field B

Field B can have multiple values for each value in Field A

When I am doing a table with Sum(Sales) and add Field B to the table the Sales amount will be doubled or tripled as the case may be.

How can I correctly show Field B without duplicating the sum sales

rogerpegler
Creator II
Creator II

The simplest solution is to not join the tables and just let Qlik associate them through the common field name.  Assuming sales is in Table A it will sum correctly. A simple UI table with Sum(Sales) and Field B as the dimension will show the Sum(Sales) amount correctly per row but potentially misleading to users as the sum of the rows may not equal the overall total. This is because a given sales amount may be associated with more than one row.

The best solution really depends on the nature of the data and the underlying business logic that is appropriate.

shair_abbas
Partner - Creator
Partner - Creator

Hi Rehan,
 When you have one to many relation then join is behaving exactly as it supposed to do. so In qlik there are two option to cater this issue.
1. Instead of using join just associate both table on key fields

2. Use Mapping load and ApplyMap() instead of using join it will get the first value of B field against A field available in the table. 

ioannaiogr
Creator II
Creator II

Hi @shair_abbas could you please elaborate on your 2nd suggestion with an example? I think I may be looking for a solution like this. Thank you!