Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Everytime i load my script to connect with a mapping table "DATES", i get an error indicating that my "TempCalendarExtremes" table cannot be found. Any ideas on what i can do to fix this problem? Please see load error below script.
TempCalendarExtremes:
LOAD
date(Min(Date)) as StartDate,
date(Max(Date)) as StopDate
RESIDENT
DATES;
LET vStartDate = num(peek('StartDate',0,'TempCalendarExtremes'));
LET vEndDate = num(peek('StopDate',0,'TempCalendarExtremes'));
LET vToday = num(Today());
DROP TABLE TempCalendarExtremes;
//DROP TABLE Calendar_Dates;
//Building the temporary calendar with just the numeric representation of the date
TempCalendar:
LOAD
$(vStartDate) + iterno() - 1 AS DateKey //The numeric representation of the date
AUTOGENERATE (1)
WHILE $(vStartDate) + iterno() - 1 <= $(vEndDate);
//Building the master calendar with most date dimensions
MasterCalendar:
LOAD
DateKey as Date,
DateKey as DateKeyCal,
date(DateKey) AS DateKey2,
// Basic Date Dimensions
year(DateKey) AS Year,
month(DateKey) AS Month,
date(monthstart(DateKey), 'MMM-YYYY') AS [Month-Year],
'Q' & ceil(month(DateKey) / 3) AS Quarter,
week(DateKey) AS Week,
weekday(DateKey + 2) AS Weekday,
day(DateKey) AS Day,
$(vToday) - DateKey AS Age
RESIDENT
TempCalendar
WHERE DateKey > 30000;
DROP TABLE TempCalendar;
ERROR:
Table not found
TempCalendarExtremes:
LOAD
date(Min(Date)) as StartDate,
date(Max(Date)) as StopDate
RESIDENT
DATES;
Any help would be most appreciated.
Thanks.
Thank you for your response.
The Master Calendar is supposed to be linked to the below date map:
DATES:
Mapping
LOAD [BW Month],
Date
FROM
(
Thank you,
Why are you using it as a Mapping load? It doesn't seem like you're actually mapping anything. If you take the Mapping part out, it should work.
Try running a debug reload and see how many records get into the TempCalendarExtremes table. I'm thinking you may not be getting any records into the table. It could be because the dates are text strings so the min and max functions are not work or something similar.
Thank you, That fixed the problem.
I am trying to fix a file that was built previously, so I am coming accross things that are not working the way they should