Aggregation issues with Keep statement

[MAK_21]

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

[hic]

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.

[TauseefKhan]

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.***

[MAK_21]

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? 

[MAK_21]

@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.