9 Replies Latest reply: Nov 19, 2012 3:56 AM by Hein Schultz RSS

    Running Total on specified fields

    Hein Schultz

      Hi guys

       

      I need some assistance please. I am looking for the code in which i can achieve the following in my LOAD script:

       

      My table gets extracted to something simular to this

       

      CustomerProductDateTime
      AngiaAAA01/01/20122
      BulgionAAA05/01/20124
      AngiaBBB10/01/20126
      AngiaAAA15/01/20123
      BulgionAAA20/01/20128
      CarfieldCCC25/01/20124
      BulgionAAA01/02/20124
      BulgionAAA05/02/20123
      CarfieldCCC10/02/20126
      AngiaBBB15/02/20123
      CarfieldCCC20/02/20123
      AngiaAAA25/02/20129

       

      So basically the table is sorted via Date. And if needed i am sure it can be Customer, then date.

       

      Now the result i'm looking for is to have a running total of Time against Customer and Product, calculated per month. This must in accordance to date. So my result should look something like this:

       

      CustomerProductDateTimeRunning Total
      AngiaAAA01/01/201222
      BulgionAAA05/01/201244
      AngiaBBB10/01/201262
      AngiaAAA15/01/201235
      BulgionAAA20/01/2012812
      CarfieldCCC25/01/201244
      BulgionAAA01/02/201244
      BulgionAAA05/02/201237
      CarfieldCCC10/02/201266
      AngiaBBB15/02/201233
      CarfieldCCC20/02/201239
      AngiaAAA25/02/201299

       

      I was able to get nearly there by using this code:

      IF(RecNo() = 1, Time,

          IF((Customer = Previous(Customer) AND Month = Previous(Month)), Time + Num(Previous(Peek(TimeRun))),Time)) AS TimeRun

       

      But the problem is it has got to be sorted by costomer and date, but it also assumes it is always the same product. And that is where my error comes in.

      It has to be sorted by date, so then the products would get mixed up and the string won't work.

       

      PLEASE HELP!