Left Join (1 to Many Relationship)

[Rehan]

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

[Rehan]

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]

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]

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]

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!