One-to-many join, duplicate sum values

Wed, 05 Aug 2009 21:19:03 GMT

Hello

I have two files, the first with unique claim number (one record per claim) and cost, and the second with non-unique claim numbers (possibly many records per claim) and a code. When I load them in to QV and sum the cost by claim number, the values are multiplied up by the number of duplicates on the second file. For example:


status:
load * inline [
claim, cost
A, 100
B, 150
C, 120
D, 90
];

status2:
LEFT JOIN load* inline [
claim, rule
A, 1
B, 1
B, 2
C, 1
D, 2
D, 3];

In this example if you create a table to sum cost by claim B comes out as 300 and D as 180.

How do I resolve this?

Many thanks!

topic One-to-many join, duplicate sum values in QlikView

One-to-many join, duplicate sum values