Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
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

3 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
Partner

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.