1 Reply Latest reply: Nov 18, 2016 6:13 AM by Stefan Wühl RSS

    RangeSum Issue

    scotly victor

      Hi Every One,

       

      I  wanted  a Rangesum of Qty by Itemcode ,Branch.

       

      Input:

      LOAD * Inline [

      Date,Itemcode,Branch,Qty

      1/1/2013,A,112,7

      1/1/2013,A,112,14

      3/1/2013,A,113,10

      4/1/2013,A,112,23

      5/1/2013,B,112,23

      5/1/2013,B,112,24

      ];

      Desired Output:

      LOAD * Inline [

      Date,Itemcode,Branch,runningQty

      1/1/2013,A,112,7

      1/1/2013,A,112,21

      3/1/2013,A,113,10

      4/1/2013,A,112,44

      5/1/2013,B,112,23

      5/1/2013,B,112,47

      ];

       

       

      Code:

      Final:

      load

      Date,

      Itemcode,

      Branch,

      if(Itemcode=peek(Itemcode) and Branch=Peek(Branch),rangesum(Qty,peek('runningQty') ),Qty) as runningQty

      resident

      Temp  order by Itemcode,Branch;


      Above code didn't work.

        • Re: RangeSum Issue
          Stefan Wühl

          I think you need to sort your Final table LOAD by date ascending, too:

           

          Final:

          load distinct

          Date,

          Itemcode,

          Branch,

          if(Itemcode=peek(Itemcode) and Branch=Peek(Branch),rangesum(Qty,peek('runningQty') ),Qty) as runningQty

          resident

          Temp  order by Itemcode,Branch, Date;



          edit:

          Input:

          LOAD * Inline [

          Date,Itemcode,Branch,Qty

          1/1/2013,A,112,7

          1/1/2013,A,112,14

          3/1/2013,A,113,10

          4/1/2013,A,112,23

          5/1/2013,B,112,23

          5/1/2013,B,112,24

          ];

          Final:

          load distinct

          Date,

          Itemcode,

          Branch,

          if(Itemcode=peek(Itemcode) and Branch=Peek(Branch),rangesum(Qty,peek('runningQty') ),Qty) as runningQty

          resident

          Input  order by Itemcode,Branch,Date;

           

          DROP TABLE Input;

           

          Branch Itemcode Date runningQty
          112A1/1/20137
          112A1/1/201321
          112A4/1/201344
          112B5/1/201323
          112B5/1/201347
          113A3/1/201310