Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

alextomlins
New Contributor II

Generating Dates from min and max date issue.

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 

2 Replies
MVP
MVP

Re: Generating Dates from min and max date issue.

Would you be able to share your script here?

alextomlins
New Contributor II

Re: Generating Dates from min and max date issue.

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;