I think you're mixing up your keys (or I'm not getting it...)
table123: LOAD * INLINE [ POitemKey, Qty, TrueKey 4500803485|00010, -5, 2016|5000200614|0001 4500803485|00010, 5, 2016|5000200613|0001 4500803485|00010, 5, 2016|5000208014|0001 ]; NoConcatenate POReceived: LOAD POitemKey as POitemKey_Open, Qty as QtyReceived, TrueKey as Key Resident table123; NoConcatenate POReceived2: LOAD POitemKey_Open, Sum(QtyReceived) as QtyFinal RESIDENT POReceived Group by POitemKey_Open; drop table POReceived;
which is what I reckon you were trying to achieve, right?
Please find the attached demo.
Hope this helps you.
With kind regards,
demo.qvw 154.8 K
I agree with your example and it does work with the inline statement, but something weird is happening. Here is an example. Every time I try to sum the quantities or group by, it does not add properly.
Things I have tried:
1) Store the table and open in a new session of qlikview.
2) Format Qty field while loading as a num() to ensure that the negative sign is not affecting it.
The only thing I can get to work is Set Analysis.
sum(Qty) always seems to double the counts which doesn't make sense because the field 'Key' is a perfect Key.
If I group by below and sum qty I would expect to get 5. But that does not happen. Very odd.
Yet if I load inline as you show, it does work.
Thanks for the help,