Autogenerating records for dates

Wed, 17 Oct 2012 19:42:08 GMT

In my script I have a portion that attempts to create records in a table that go back 1 year from the next month.

So today is 10/17/2012. I want to make a table with the following:

11/30/2012

10/31/2012

9/30/2012

8/31/2012

7/31/2012

6/30/2012

5/31/2012

4/30/2012

3/31/2012

2/29/2012

1/31/2012

12/31/2011

11/30/2011

It needs to always be at the end of the month since that is my rollover date.

I sort of have it, but I think my logic for jumping forward during my LOAD is the issue. Problem is, I don't know what my other option would be?

In the code below, varSRMaxCreateDate is just an active variable that gets the latest record date in the file.

LET RollMax = Num(MonthEnd(varSRMaxCreateDate));
LET RollMin = Num(MonthEnd(Addmonths(varSRMaxCreateDate,-12)));
trace RollMin: $(RollMin);
trace RollMax: $(RollMax);
RollingCalendar:
LOAD
Date($(RollMin) + RecNo() * 30) AS RollDate,
1 as RollKey
AUTOGENERATE 13;

This is the resulting data. You can see how not all months are at the ending date.

RollDate

12/1/2011

12/31/2011

1/30/2012

2/29/2012

3/30/2012

4/29/2012

5/29/2012

6/28/2012

7/28/2012

8/27/2012

9/26/2012

10/26/2012

11/25/2012

Re: Autogenerating records for dates

johnw — Wed, 17 Oct 2012 19:52:49 GMT

I think this:

date(daystart(addmonths($(RollMin),recno()-1,1))) as RollDate

Re: Autogenerating records for dates

Wed, 17 Oct 2012 19:56:03 GMT

John,

That is beautiful! It worked perfectly!

Thanks!

topic Autogenerating records for dates in QlikView

Autogenerating records for dates

Re: Autogenerating records for dates

Re: Autogenerating records for dates