Error creating master calendar

[ashmitp869]

Hi there,

I am getting the below error when generating master calendar -

Error on TempCal part :

My script :

SET CreateSearchIndexOnReload=0 ;

SUB CREATING_MASTER_CALENDAR (FIELDNAME, TABLENAME)
//Master Calendar
Let _FIELDNAME = PurgeChar(FIELDNAME,'"[]');
Let _TABLENAME = PurgeChar(TABLENAME,'"[]');

/*************** MinMax Table *************

Keeps minimum and maximum Date value from Facts table

*/

MinMax:
LOAD
Min([$(_FIELDNAME)]) as MinDate,
Max([$(_FIELDNAME)]) as MaxDate
RESIDENT [$(_TABLENAME)];

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));
LET vToday = $(vMaxDate);

/*************** Temporary Calendar *************

Generates a single table with one field containing
all existing dates between MinDate and MaxDate.

*/

TempCal:
LOAD
date($(vMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;

DROP TABLE MinMax;

/*************** Master Calendar ***************

Disconnected during the Date Island exercise by renaming TempDate as IslandDate

*/

[MasterCalendar$(_FIELDNAME)]:
LOAD
TempDate AS [$(_FIELDNAME)],
Day(TempDate) AS [$(_FIELDNAME).Day],
Weekday(TempDate) AS [$(_FIELDNAME).WeekDay],
Week(TempDate) AS [$(_FIELDNAME).Week],
Month(TempDate) AS [$(_FIELDNAME).Month],
Year(TempDate) AS [$(_FIELDNAME).Year],
If(Num(Month(TempDate))>6,Year(TempDate)&'/'&Year(addmonths(TempDate,12)),Year(Addmonths(TempDate,-12))&'/'&Year(TempDate)) AS [$(_FIELDNAME).FinancialYear],
Week(TempDate)&'-'&Year(TempDate) AS [$(_FIELDNAME).WeekYear],
Date(monthstart(TempDate), 'MMM-YYYY') AS [$(_FIELDNAME).MonthYear],
'Q' & ceil(month(TempDate) / 3) AS [$(_FIELDNAME).Quarter]
RESIDENT TempCal
ORDER BY TempDate ASC;

DROP TABLE TempCal;
ENDSUB

Date data sample :

Thanks

[MayilVahanan]

HI @ashmitp869 

Based on error msg, it indicates that, there is no values in vMinDate  & vMaxDate variables. 

You can check the variable values & trace comments to find out the error. 

Or use debug mode and monitor the variables value.

[ashmitp869]

Hii Mayil,

I am getting value for vMinDate

and vMaxDate as 

[Vegar]

There is an issue with your variables. Check each step you do creating variables towards the template generation.

You should probably edit the _FIELDNAME and _TABLENAME declarations like this:

//Master Calendar
Let _FIELDNAME = PurgeChar( '$(FIELDNAME)','"[]');
Let _TABLENAME = PurgeChar('$(TABLENAME)','"[]');