Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Hii Mayil,
I am getting value for vMinDate
and vMaxDate as
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)','"[]');