Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
rhalfar
Partner
Partner

merging almost identical records into 1 record while giving the sum of 1specific field

Here is the deal:

rhalfar_0-1616519027682.png

rhalfar_1-1616519593823.pngrhalfar_2-1616519652964.png

 

I do a left join on 2 tables : 1 table for Invoices ('Verkoop' ) And 1 table for Orders (Archief_Temp).
So I have a Table Verkoop with both my invoices with Artikels and Archief_Temp with the original Orders and the Artikels.
I have to calculate the service level of the deliveries to the customers, so I have to get the sum of the Quantities on the invoice and the sum of the quantities on the Order.

But when I calculate the sum of the quantities it get 2 times the value that I should get.
In this specific Case, I do a selection and I get 544 instead of 277.

Can someone please help.

 

@sunny_talwar , @swuehl 

1 Solution

Accepted Solutions
rhalfar
Partner
Partner
Author

"if it does then one solution is not to join them but keep them is separate tables"

This was the solution to my problem. I created 2 seperate tables which, with the right keys of course, automatically connected to the fact table. 

View solution in original post

4 Replies
edwin
Specialist III
Specialist III

you are definitely joining a one to many related tables.  i suggest you run this step by step so you can identify which join is multiplying your records.

for example add an EXIT SCRIPT in position 1, then reload and check the number of rows.  remove the exit script and add it to position 2, reload and check:

edwin_0-1616520777036.png

this will tell you if you are adding a 1 to many table - this will of course multiply your records.  if it does then one solution is not to join them but keep them is separate tables.  you will of course need to change their associative keys to prevent synthetic keys.
hope that helps

rhalfar
Partner
Partner
Author

Hallo Edwin,

Thanks for your advice.

edwin
Specialist III
Specialist III

np

rhalfar
Partner
Partner
Author

"if it does then one solution is not to join them but keep them is separate tables"

This was the solution to my problem. I created 2 seperate tables which, with the right keys of course, automatically connected to the fact table. 

View solution in original post