topic Re: Left Join (1 to Many Relationship) in App Development

Left Join (1 to Many Relationship)

Rehan — Thu, 23 May 2019 00:17:32 GMT

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

Re: Left Join (1 to Many Relationship)

Rehan — Thu, 23 May 2019 00:19:18 GMT

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

Re: Left Join (1 to Many Relationship)

rogerpegler — Thu, 23 May 2019 06:45:19 GMT

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.

Re: Left Join (1 to Many Relationship)

shair_abbas — Thu, 23 May 2019 07:15:31 GMT

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.

Re: Left Join (1 to Many Relationship)

ioannaiogr — Sat, 06 May 2023 19:44:05 GMT

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!