2 Replies Latest reply: Jun 1, 2014 3:31 PM by Marco Wedel RSS

    "Generating missing data" with Months steps and not Days steps

    Rodrigo Giner

      Hi forum,

       

      I have readed and I'm aware of the doc "Generating missing data" I used it and works, now I have to do the same but instead of generating missing data with days steps, I needed to it by months steps.

       

      I have this:

      Rates:

      LOAD * INLINE [

          Date, Rate

          01.2014, 2

          03.2014, 4

          08.2014, 8

      ];

       

       

      I need, this output

       

      Date,      Rate

      01.2014,  2

      02.2014,  2

      03.2014,  4

      04.2014,  4

      05.2014,  4

      06.2014,  4

      07.2014,  4

      08.2014,  8

      This is code working with days steps, I need to do exactly the same but with months steps, I also leave the example code, just in case.

       

      TempTable_Rates:

      Load

          Fecha AS Date,

          Rate Resident Rates ;

       

      MinMaxDate:

      Load

          Min(Date) as MinDate,

          Max(Date) as MaxDate

      resident TempTable_Rates;

       

      Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;

      Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;

      Drop Table MinMaxDate;

       

      Join (TempTable_Rates)

      Load

          Date(recno()+$(vMinDate)) as Date

          Autogenerate vMaxDate - vMinDate;

       

      Rates:

      NoConcatenate

      Load

          Date(Date,'YYYY-MM-DD'),

          If( IsNull( Rate ), Peek( Rate ), Rate ) as Rate

      Resident TempTable_Rates

      Order By Date ; // so that above values can be propagated downwards

       

      Drop Table TempTable_Rates;

      Drop Table Rates;

       

      Can some1 help me?