topic Re: Aggregation issues with Keep statement in App Development

Aggregation issues with Keep statement

MAK_21 — Fri, 07 Jun 2024 19:23:04 GMT

Hi,
We ran into aggregation issues with "Keep" statement. When I change Keep to Join, the aggregation works as expected. Here is the script and what's expected.

X:
LOAD * Inline[
TICKET_NBR, TRAN_DATE_KEY, TRAN_NBR, TRAN_KEY
A1, 10, 1, 1000
A1, 10, 2, 1000
A1, 11, 3, 1001
A2, 10, 1, 1000
A2, 10, 2, 1001
A3, 10, 1, 1000
A3, 10, 2, 1001
A3, 11, 3, 1000
];
Left Keep (X)
Y:
LOAD * Inline[
TRAN_KEY, AMT
1000, 200
1001, 500
1002, 100
];

Straight table
For TICKET_NBR A1, TRAN_KEY 1000
Count(AMT) should be 2
Sum(AMT) should be 400
Count(TRAN_KEY) is 2 and it is correct
Sum(TRAN_KEY) is 2000 and it is correct
Same case with TKT_NBR A3 as well.
Kindly look into this and advise a solution. I have attached the qvw for reference.
Thanks,
Mahesh

Re: Aggregation issues with Keep statement

hic — Fri, 07 Jun 2024 21:12:45 GMT

The Qlik Engine is calculating correctly. If you use a "Left Keep" you will get

and then Count(AMT) is 1 and Sum(AMT) 200. See the yellow marked cells.

However, if you join the tables, you will get

and then the numbers change. Count(AMT) is now 2 and Sum(AMT) is 400.

This is the way it should be.

Re: Aggregation issues with Keep statement

TauseefKhan — Sat, 08 Jun 2024 16:09:37 GMT

Hi @MAK_21,

If you must keep the tables separate for some reason, you can use a mapping load to create a mapping table that will allow you to associate the AMT values with the TRAN_KEY without duplicating rows as your required output.

Table_2:
Mapping Load
* Inline [
TRAN_KEY, AMT
1000, 200
1001, 500
1002, 100
];

X:
LOAD *,
ApplyMap('Table_2',TRAN_KEY,0) As AMT
Inline [
TICKET_NBR, TRAN_DATE_KEY, TRAN_NBR, TRAN_KEY
A1, 10, 1, 1000
A1, 10, 2, 1000
A1, 11, 3, 1001
A2, 10, 1, 1000
A2, 10, 2, 1001
A3, 10, 1, 1000
A3, 10, 2, 1001
A3, 11, 3, 1000
];

***Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.***

Re: Aggregation issues with Keep statement

MAK_21 — Tue, 11 Jun 2024 13:54:04 GMT

Thank you for responding to my question.

For testing purposes, I would like to try the same aggregation on another Number field TRAN_KEY and the numbers come correct. Count(TRAN_KEY) is 2 and Sum(TRAN_KEY) is 2000. I initially thought it might be because of these fields present in the Left table X but when I change the association to Right Keep or Inner Keep, they are still unchanged. Can you please throw some light on it?

Re: Aggregation issues with Keep statement

MAK_21 — Wed, 12 Jun 2024 19:51:31 GMT

@TauseefKhan , Thanks so much for advising alternate solution. We have a lot of tables associated using Keep across our apps, I am worried if the aggregated values are correct. Let me try to fit your solution into our data model and see how it works. Thanks again.