Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to group by purchase orders to count the total qty.
The results are double counting.
I have a unique key for each row and have tried autonumber, both have failed in reducing the duplication.
Issue:
result should be Qty 5.
Group by incorrectly return Qty 10.
Raw data:
POitemKey | Qty | TrueKey |
4500803485|00010 | -5 | 2016|5000200614|0001 |
4500803485|00010 | 5 | 2016|5000200613|0001 |
4500803485|00010 | 5 | 2016|5000208014|0001 |
Group by code...
NoConcatenate
POReceived:
LOAD
POitemKey as POitemKey_Open,
Qty as QtyReceived,
Key
From table123
Where [MVT_Type]='101' OR [MVT_Type]='102'; //receipts and returns
NoConcatenate
POReceived2:
LOAD
POitemKey,
Sum(Qty) as QtyFinal
RESIDENT POReceived
Group by POitemKey;
drop table POReceived;
Results after groupby
POitemKey | QtyFinal |
4500803485|00010 | 10 |
Math...[=5+5+(-5) = 5]Result should be QtyFinal = 5.
I have also tried autonumber.
Hi Brandon,
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;
produces:
which is what I reckon you were trying to achieve, right?
Please find the attached demo.
Hope this helps you.
With kind regards,
Ronald
Hi Brandon,
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;
produces:
which is what I reckon you were trying to achieve, right?
Please find the attached demo.
Hope this helps you.
With kind regards,
Ronald
Hi Ronald,
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.
3) NoConcatenate
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,
Brandon
Hi Brandon,
I suspect your source table has duplicate entries.
Can you load them with RecNo() to ensure every row is unique?
See: rowno() and recno()
With kind regards,
Ronald