Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Double counting of values with Group by function

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:

  

POitemKeyQtyTrueKey
4500803485|00010-52016|5000200614|0001
4500803485|0001052016|5000200613|0001
4500803485|0001052016|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

  

POitemKeyQtyFinal
4500803485|0001010

  

Math...[=5+5+(-5) = 5]Result should be QtyFinal = 5.

I have also tried autonumber.

Please any suggestions are greatly appreciated!!!

1 Solution

Accepted Solutions
RonaldDoes
Partner - Creator III
Partner - Creator III

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:

Naamloos.png

which is what I reckon you were trying to achieve, right?

Please find the attached demo.

Hope this helps you.

With kind regards,

Ronald

View solution in original post

3 Replies
RonaldDoes
Partner - Creator III
Partner - Creator III

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:

Naamloos.png

which is what I reckon you were trying to achieve, right?

Please find the attached demo.

Hope this helps you.

With kind regards,

Ronald

Anonymous
Not applicable
Author

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.

Example.PNG

Thanks for the help,

Brandon

RonaldDoes
Partner - Creator III
Partner - Creator III

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