Duplicate sum values in a one-to-many join

[#1]

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];

I this example if you create a table to sum cost by claim, claim B will show 300 and D 180.

How do I resolve this?

Many thanks!

[Anonymous]

Regarding QV9 - I've done a couple of production applications with it. First impression is that it works like 8.50, speaking of performance, but it's too early to tell.

[tripatirao]

Hi,

sum(aggr(cost, claim)) this expression is giving correct result

but

aggr(sum(cost),claim) and sum(aggr(sum(cost),claim))and this is not giving correct result.

when i taking claim as dimension in straight table.