Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JustinDallas
Specialist III
Specialist III

Load Dates between two date variables for a calendar

Hello Folks,

 

I'm using LET to create a start and end date.  Then, I would like to load the dates between said two dates to make a Calendar.  For some reason, I can't figure out what's going wrong for the life of me.

 

LET vStartDate = Num(YearStart('01/01/2018'))
;
LET vEndDate = Num(YearEnd('01/01/2018'))
;

MasterCalendar:
Load 
 TempDate AS '%calendar_date_key',
 TempDate As CalendarDate
;
LOAD 
 date($(vStartDate) + IterNo()) AS TempDate
WHILE $(vStartDate) + IterNo() <= $(vEndDate)
;

After the script is done running, I have no data loaded in the data model.  I know it's something stupid simple, but I'm apparently blind to it.

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

I think you missed AutoGenerate 1 before your While statement... you can also try this

LET vStartDate = Num(YearStart(MakeDate(2018)));
LET vEndDate = Num(Floor(YearEnd(MakeDate(2018))));

TRACE $(vStartDate);
TRACE $(vEndDate);

MasterCalendar:
LOAD TempDate AS '%calendar_date_key',
 	 TempDate As CalendarDate;
LOAD Date($(vStartDate) + IterNo()) AS TempDate
AutoGenerate 1
While $(vStartDate) + IterNo() <= $(vEndDate);

View solution in original post

5 Replies
pradosh_thakur
Master II
Master II

LET vStartDate = Num(YearStart('01/01/2018'))
;
LET vEndDate = Num(YearEnd('01/01/2018'))
;

MasterCalendar:
Load 
 TempDate AS '%calendar_date_key',
 TempDate As CalendarDate
;
LOAD 
 date($(vStartDate) + IterNo()) AS TempDate
WHILE $(vStartDate) + IterNo() <= $(vEndDate)
;


Load * inline [
dim
1
2
3
];
Learning never stops.
sunny_talwar

I think you missed AutoGenerate 1 before your While statement... you can also try this

LET vStartDate = Num(YearStart(MakeDate(2018)));
LET vEndDate = Num(Floor(YearEnd(MakeDate(2018))));

TRACE $(vStartDate);
TRACE $(vEndDate);

MasterCalendar:
LOAD TempDate AS '%calendar_date_key',
 	 TempDate As CalendarDate;
LOAD Date($(vStartDate) + IterNo()) AS TempDate
AutoGenerate 1
While $(vStartDate) + IterNo() <= $(vEndDate);
pradosh_thakur
Master II
Master II

Hi Sunny,

the above script when i loaded without the inline load returned no data but with inline load it is returning rows. What is the reason?

Thanks
Pradosh
Learning never stops.
sunny_talwar

The Inline is working as load before preceding load for the Master Calendar... but if you notice since you have 3 values in dim, the calendar is getting duplicated 3 times... so instead of 364, we loaded 1092 rows

image.png

Better to just add AutoGenerate 1

JustinDallas
Specialist III
Specialist III
Author

And they say the Dodo is extinct, yet here I sit...