Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
JustinDallas
Valued Contributor II

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 (4)
1 Solution

Accepted Solutions

Re: Load Dates between two date variables for a calendar

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);
5 Replies
pradosh_thakur
Honored Contributor II

Re: Load Dates between two date variables for a calendar

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.

Re: Load Dates between two date variables for a calendar

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
Honored Contributor II

Re: Load Dates between two date variables for a calendar

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.

Re: Load Dates between two date variables for a calendar

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
Valued Contributor II

Re: Load Dates between two date variables for a calendar

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