topic Re: Sum return incorrect result due to duplicates hidden in QlikView

Sum return incorrect result due to duplicates hidden

Black_Hole — Sat, 16 Nov 2024 02:26:12 GMT

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.

Re: Sum return incorrect result due to duplicates hidden

Black_Hole — Thu, 19 Sep 2019 22:10:24 GMT

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.

Re: Sum return incorrect result due to duplicates hidden

Arthur_Fong — Thu, 19 Sep 2019 23:56:59 GMT

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

Re: Sum return incorrect result due to duplicates hidden

Black_Hole — Fri, 20 Sep 2019 09:27:47 GMT

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,

Re: Sum return incorrect result due to duplicates hidden

StarinieriG — Fri, 20 Sep 2019 09:41:58 GMT

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))

Re: Sum return incorrect result due to duplicates hidden

Black_Hole — Fri, 20 Sep 2019 09:47:53 GMT

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.

Re: Sum return incorrect result due to duplicates hidden

StarinieriG — Fri, 20 Sep 2019 09:50:33 GMT

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

Re: Sum return incorrect result due to duplicates hidden

Black_Hole — Fri, 20 Sep 2019 10:40:34 GMT

Re @StarinieriG ,

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

Many thanks for your great help!