Using iterno() and While to generate conditional dates

[#1]

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

start_date	company_name	pay_model
2015-10-01	Company1	1
2015-12-01	Company1	2
2015-12-01	Company2	1
2016-01-01	Company3	2
2015-12-01	Company4	6

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_date	company_name	pay_model
2015-10-01	Company1	1
2015-11-01	Company1	1
2015-12-01	Company1	2
2016-01-01	Company1	2
2015-12-01	Company2	1
2016-01-01	Company2	1
2016-01-01	Company3	2
2015-12-01	Company4	6
2016-01-01	Company4	6

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_date	company_name	pay_model
2015-10-01	Company1	1
2015-11-01	Company1	1
2015-12-01	Company1	1
2015-12-01	Company1	2
2016-01-01	Company1	1
2016-01-01	Company1	2
2015-12-01	Company2	1
2016-01-01	Company2	1
2016-01-01	Company3	2
2015-12-01	Company4	6
2016-01-01	Company4	6

Any ideas on how I can correct this?

[swuehl · Accepted solution]

You can do it like shown here to create intervals for each record (where you can fill in 2016-01-01 for companies with a single record or for it's last period):

Creating a Date Interval from a Single Date

Then execute the existing code using the end date instead of Makeddate(2016,1)

[#3]

That solution would have worked, I however decided on another as time was of the essence. I however think your suggestion is more stable as data structures may change in the future. My solution only includes changing the "While" statement to the following:

While AddMonths(ERSdate,iterno()-1) <= Makeddate(2016,1) and if(Peek('company_name', recno(), 'input') = [company_name], if(AddMonths(start_date,iterno()-1) < Peek('start_date', recno(), 'input'), 1, 0), 1);