Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $400 before Dec 31st: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using iterno() and While to generate conditional dates

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?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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)

View solution in original post

2 Replies
swuehl
MVP
MVP

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)

Not applicable
Author

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);