Sum return incorrect result due to duplicates hidden

[Black_Hole]

Hello all,

I try to calculate the amount of the sales, the  quantity of the current stock, the amount received and the amount consumed.

But, the result returned by Group By is not correct at all !

After some research, in this forum, I find a topic when a "Qlikviewer"  advise to use a count on the key.

Normally, the result of the count(key) must be equal to one. But, in my case, the result is 8.

How can I avoid this problem in order to return the correct result.

• Below the logic behind my script:

JOIN(REF_TAB)

TAB_CALCUL:

LOAD DISTINCT 

Key_ID,

ROUND(SUM(Sales),0.01) AS TotSales,
ROUND(SUM(Receipt)-SUM(Consumption),0.01) AS Stock,
ROUND(SUM(Receipt) ,0.01) AS AmountReceipt,
ROUND(SUM(Consumption),0.01) AS AmountConsump

RESIDENT REF_TAB

GROUP BY KeyID;

Thank you in advance for your help.

[Black_Hole]

Hi all,

Any help to lead me toward the resolution of this problem.

Please tell me if my request is not clear.

Thank you in advance for your help.

[Arthur_Fong]

Why do you join the aggregated table back to the original table?

Imagine if you have a table of 10 rows with a unique key, then perform a group by.

Result will return one row for that table.

Next, you join back to the original table with the same key, which will lead to one-to-many join.

Try to not join back to original table, use a key to link between the aggregated table and the original table.

In this case, you do not need to create composite key as you have only one key field in your table. 

Insert a comment your join statement will do.

Thanks and regards,

Arthur Fong

[Black_Hole]

Hello Arthur (@Arthur_Fong),

Thank you for your reply.

I need to join to the original because the variable calculated is used in another resident table.

If I don't join the variable is not found.

Please could you advise what I can in this case to resolve this problem of duplicates.

Thank you in advance for your help.

Kind regards,

[StarinieriG · Accepted solution]

Hi,

the problem is the join, because the original table has multiple rows with the same id, so the result of group by was repeated for each row, with this logic.

Maybe you could do something like this if you use this value in a table, to avoid duplicated:

example

Sum(Aggr(Max(TotSales), KeyID))

[Black_Hole]

Hello  @StarinieriG ,

I will try your solution.

Just to be sure, the expression Sum(Aggr(Max(TotSales), KeyID))  can be implemented in a load statement ?

Thank you in advance for your help.

[StarinieriG]

No, because if you want to avoid duplicate rows in load statement you couldn't use the join

[Black_Hole]

Re @StarinieriG ,

I understand. So, I try your solution in an expression and the result is correct.

Many thanks for your great help!