2 Replies Latest reply: Jun 3, 2016 10:56 AM by Ludvig Ã…berg RSS

    Using iterno() and While to generate conditional dates

    Ludvig Ã…berg

      Hello! I have data in the format of the table below:

       

      start_datecompany_namepay_model
      2015-10-01Company11
      2015-12-01Company12
      2015-12-01Company21
      2016-01-01Company32
      2015-12-01Company46

       

      I want to expand the table to showing dates up to 2016-01-01 for each company while taking the new pay_model of Company2 into account:

       

      start_datecompany_namepay_model
      2015-10-01Company1

      1

      2015-11-01Company1

      1

      2015-12-01Company12
      2016-01-01Company12
      2015-12-01Company21
      2016-01-01Company21
      2016-01-01Company32
      2015-12-01Company4

      6

      2016-01-01Company46

       

      So far I've used the followng script:

       

      LOAD

           *,

           Date(addmonths(start_date, iterno() -1)) as start_date,

      FROM [input]

      While AddMonths(start_date,iterno()-1) <= Makedate(2016,1);

       

      Which works fine for generating the missing dates, but this causes Company1 to get rows for pay_model 1 and 2 from 2015-12-01:

       

      start_datecompany_namepay_model
      2015-10-01Company1

      1

      2015-11-01Company1

      1

      2015-12-01Company11
      2015-12-01Company12
      2016-01-01Company11
      2016-01-01Company12
      2015-12-01Company21
      2016-01-01Company21
      2016-01-01Company32
      2015-12-01Company4

      6

      2016-01-01Company46

       

      Any ideas on how I can correct this?