Load Dates between two date variables for a calendar

[JustinDallas]

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.

[pradosh_thakur]

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
];

[sunny_talwar · Accepted solution]

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]

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

[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

Better to just add AutoGenerate 1

[JustinDallas]

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