Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Black_Hole
Creator II
Creator II

Sum return incorrect result due to duplicates hidden

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.

 

Labels (4)
1 Solution

Accepted Solutions
StarinieriG
Partner
Partner

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

View solution in original post

7 Replies
Black_Hole
Creator II
Creator II
Author

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

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
Creator II
Creator II
Author

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

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

View solution in original post

Black_Hole
Creator II
Creator II
Author

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

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

Black_Hole
Creator II
Creator II
Author

Re @StarinieriG ,

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

Many thanks for your great help!