2 Replies Latest reply: Aug 22, 2011 3:22 PM by Rakesh Mehta RSS

    Autogenerate function

      I have a startdate and a enddate of a customer, for example:

      Customer     Startdate          Enddate               Value

      1                  01-01-2011       15-04-2011          10

      1                  16-04-2011       31-12-2011          20

      2                  01-01-2011       17-05-2011          15

       

      How can I get the dates between those two datefields for each customer?

        • Re: Autogenerate function
          Jonathan Dienst

          Hi

           

          To calculate in a dimension:

           

           

               =Date#(Enddate, 'dd-mm-yyyy') - Date#(Startdate, 'dd-mm-yyyy;)

           

          However, if you ensure that these values are dates in your load script, like this...

           

           

          LOAD
               Customer, 
               Date(Date#(Startdate, 'dd-mm-yyyy')) AS Startdate,
               Date(Date#(Enddate, 'dd-mm-yyyy')) AS Enddate,
          ...
          FROM ...;
          

           

          Then

           

               =Enddate - Startdate

           

          will do the job.

           

          Hope that helps

          Jonathan

          • Autogenerate function
            Rakesh Mehta

            Is this what you are looking for?

             

            [code]

            Data:

            LOAD * INLINE [

            Customer, Startdate, Enddate, Value

            1, 01/01/2011, 04/15/2011, 10

            1, 04/16/2011, 12/31/2011, 20

            2, 01/01/2011, 05/17/2011, 15

            ];

             

            NewData:

            LOAD Customer,

                 date(Startdate + IterNo()-1) as Date,

                 Value

            RESIDENT Data

            While Startdate + IterNo() <= Enddate+1;

            [/code]