2 Replies Latest reply: Nov 15, 2013 3:11 PM by Ahmet Erispaha RSS

    Running Totals

      I am looking to count the ID two different ways.  The first way is to count the ID and have the field 'Count' change every time the ID changes.  The second way is to count 'Count2' the ID and reset after a new ID.  I got the Count2 to work with a basic rowno().

       

      Does anyone know a way to get my 'Count' to work?  I have displayed an examply below.  Thanks everyone.

       

      IDCountCount2
      111
      112
      113
      221
      331
      332
      441
      442
      551
      661
        • Re: Running Totals
          Nagaian Krishnamoorthy

          I got the other Count also to work to get the result below:

           

          New Bitmap Image.bmp

          The script used was:

           

          Tab1:

          LOAD *, RowNo() as xx;

          LOAD * Inline [

          ID

          101

          101

          101

          102

          103

          103

          104

          104

          105

          106

          ];

           

          Tab2:

          LOAD *, RowNo() as Count1;

          LOAD ID, Sum(xx) Resident Tab1 Group By ID;

           

          Left Join (Tab1) LOAD ID, Count1 Resident Tab2;

          DROP Table Tab2;

           

          Tab3:

          NoConcatenate LOAD ID, Count1, if(ID <> Peek('ID'),1,Peek('Count2')+1) as Count2 Resident Tab1 Order By xx;

          DROP Table Tab1;

           

          Hope this helps.