2 Replies Latest reply: Jan 27, 2014 7:21 AM by Joonas Jäspi RSS

    Help in script

      Dear All, I'll appreciate your help in how to make a clculations within the sript.

       

      I would like to calculate our customer-base with the following logic:

      The data looks like this

       

      IDConnect DateDis-Connect Date
      101/01/200401/01/2004
      202/01/200405/01/2004
      302/01/200403/01/2004
      402/01/2004
      502/01/200404/01/2004
      605/01/2004
      705/01/200407/01/2004
      805/01/200410/01/2004
      905/01/2004
      1005/01/2004
      1109/01/200412/01/2004
      1209/01/2004

       

       

      At each date I would like to have a number which is a calculation of the existing connected customers.

      Means that, on the date of 2/1/2004 i will have 4 customers (5 connected till 2/1/04 and one dis-connected).

      On the Feb/5 I will have 6 customers (10 connected and 4 dis-connected).

       

      I'll appreciate your help on how to handle this in the script.

       

      Thanks in advance

      David

        • Re: Help in script
          Lucian Cotea

          First step is to transform your data to this:

           

          IDDateActionCounter
          11/1/2004Connect1
          11/1/2004Disconnect-1
          22/1/2004Connect1
          32/1/2004Connect1
          42/1/2004Connect1
          52/1/2004Connect1
          33/1/2004Disconnect-1
          54/1/2004Disconnect-1
          65/1/2004Connect1
          75/1/2004Connect1
          85/1/2004Connect1
          95/1/2004Connect1
          105/1/2004Connect1
          25/1/2004Disconnect-1
          77/1/2004Disconnect-1
          119/1/2004Connect1
          129/1/2004Connect1
          810/1/2004Disconnect-1
          1112/1/2004Disconnect-1

           

           

          Then, it's a simply "Counter" sum operation.

          • Re: Help in script
            Joonas Jäspi

            I would create separate Flag table with this:

            LOAD

            ID,

            date([Connect Date]+Iterno()-1) as Date

            if(iterno()=1, 1,0) as Flag_Start,

            if[Connect Date]+iterno()-1= [Dis-Connect Date],0,1) as Flag_Connected

            if[Connect Date]+iterno()-1= [Dis-Connect Date]1,0) as Flag_Disconnected

            Resident Data

            while [Connect Date]+iterno()-1) <= IF(isnull([Dis-Connect Date]),today(),[Dis-Connect Date]);

             

            With this you can easily get number of connected ID with Sum(Distinct {<Flag_Connected={1}>} ID), but you can easily answer question like how many customers connected/disconnected this day/this month/this year by summing start and end flags.