ERROR: LOAD Dates between two date variables for a calendar

[francesctesi]

hi,

I am trying to create a calendar composed of dates within two values.

I start from a table "EXAM", where i extract the min and max of date with the script:

Min_Max:
Load
num(Min(date)) as MinDate,
num(Max(date)) as MaxDate,
date(Min(date)) as max2
Resident EXAM;
Let vMinDate = Peek('MinDate',0,'Min_Max');
Let vMaxDate = Peek('MaxDate',0,'Min_Max');

where at the end vMinDate = 36526,5 and vMaxDate = 450006,455428281

I'm triyng to create the calendar after with the script:

MasterCalendar:
LOAD TempDate AS 'key_calendar',
TempDate As CalendarDate;
LOAD Date($(vMinDate) + IterNo()) AS TempDate
AutoGenerate 1
While $(vMinDate) + IterNo() <= $(vMaxDate)

BUT I HAVE THIS ERROR:

Unexpected token: ',', expected one of: 'OPERATOR_PLUS',

[MartW · Accepted solution]

the error is explainartory. it is saying that you have a comma (,) in the script that qlik didn't expect.

why is this happening.

lets look at your code:

in_Max:
Load
num(Min(date)) as MinDate,
num(Max(date)) as MaxDate,
date(Min(date)) as max2
Resident EXAM;
Let vMinDate = Peek('MinDate',0,'Min_Max');
Let vMaxDate = Peek('MaxDate',0,'Min_Max');


where at the end vMinDate = 36526,5 and vMaxDate = 450006,455428281

I'm triyng to create the calendar after with the script:

MasterCalendar:
LOAD TempDate AS 'key_calendar',
TempDate As CalendarDate;
LOAD Date($(vMinDate) + IterNo()) AS TempDate
AutoGenerate 1
While $(vMinDate) + IterNo() <= $(vMaxDate)

first you are loading in a tabel and getting the max and min values. all is fine until now.

let's say that you have a day 1-1-2020 8:00 this is equal to 43831,3333333333. this is where the error occurs.

you can save this value into a variable no problem but when you try to us this in your master calendar script there the error occurs. 

the best way you can resolve this is to floor the numeric value (what data values in Qlik are) to there whole number (1 - 2 - 3 - etc. instead of 1.2 - 2.2 - 3.2 - etc.)  
so change your Peek statement to 

Let vMinDate = floor(Peek('MinDate',0,'Min_Max'));
Let vMaxDate = floor(Peek('MaxDate',0,'Min_Max'));

also a quick note that this script creates a calender till the year 3132