Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
bnierhoff
Contributor
Contributor

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
Partner

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
Partner

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

bnierhoff
Contributor
Contributor
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
Partner

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