Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
bnierhoff
New 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
Contributor III

Re: Double counting of values with Group by function

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

3 Replies
RonaldDoes
Contributor III

Re: Double counting of values with Group by function

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

bnierhoff
New Contributor

Re: Double counting of values with Group by function

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
Contributor III

Re: Double counting of values with Group by function

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

Community Browser