3 Replies Latest reply: Oct 25, 2017 11:06 AM by Ronald van der Does RSS

    Double counting of values with Group by function

    Brandon Nierhoff

      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!!!

        • Re: Double counting of values with Group by function
          Ronald van der Does

          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

            • Re: Double counting of values with Group by function
              Brandon Nierhoff

              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