Generating Dates from min and max date issue.

[alextomlins]

Hi guys,

As i'm learning Qlik sense i thought I would try and auto generate a calendar for fun and to see if i could. 

The issue i'm having is when i run this very simple script it seems to get stuck in a loop and generate millions of rows. I don't just want a fix if that's OK - I would ideally like to know what is wrong , why and potential ways of solving the issues.

Thank you ever so much in advance - this community is fantastic and hope that i could one day get good enough to help others as i have been helped!

Thanks,

Alex 

[sunny_talwar]

Would you be able to share your script here?

[alextomlins]

I've got it working lol I keep fixing these things after i ask them! This is what i have now - but i don't think it's the best way of doing it Please let me know if there's something i should do because i think i have too many tables. I have a master calendar script. I just wanted to see if i could build a daily one myself - just for practice/training and to understand things like rowno iterno etc.  I think my first issue was that i was trying to use a field i was trying to generate in a load statement in the same load statement - which i think isn't possible

Temp:

LOAD

MinDate as MinDate,

today() as MaxDate

INLINE [

MinDate

09/04/1988

]

;

Let vStartDate = num(Peek('MinDate', '0', 'Temp'));

Let vEndDate = num(Peek('MaxDate', '0', 'Temp'));

Let vNumberOfDays = $(vEndDate) - $(vStartDate);

Table2:

Load

-1 + IterNo() as [num]

Autogenerate 1 WHILE -1 + IterNo() <= $(vNumberOfDays);

Table3:

Load

date($(vStartDate) + num)as [tempdate]

Resident Table2;

MasterCalendar:

Load

tempdate as Date,

MONTH(tempdate) as Month

,WEEK(tempdate) as Week

,WeekDay(tempdate) as WeekDay

,MonthName(tempdate) as MonthName

,year(tempdate) as Year

,WeekName(tempdate) as WeekName

,WeekStart(tempdate) as WeekStart

resident Table3;

Drop table Temp;

Drop table Table2;

Drop table Table3;