im trying to create a master calendar on the COMPLETION_DATE but keep getting a 'Field 'a' not found error'.
MAPPING LOAD rowno() as Month, 'Q' & Ceil (rowno()/3) as Quarter AUTOGENERATE (12);
Temp: Load min(COMPLETION_DATE) as minDate, max(COMPLETION_DATE) as maxDate Resident production;
Let varMinDate = Num(Peek('minDate', 0, 'Temp')); Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar: LOAD $(varMinDate) + IterNo()-1 As Num, Date($(varMinDate) + IterNo() - 1) as TempDate AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
TempDate AS COMPLETION_DATE, week(TempDate) As P_Week, Year(TempDate) As P_Year, Month(TempDate) As P_Month, Day(TempDate) As P_Day, YeartoDate(TempDate)*-1 as P_CurYTDFlag, YeartoDate(TempDate,-1)*-1 as P_LastYTDFlag, inyear(TempDate, Monthstart($(varMaxDate)),-1) as P_RC12, date(monthstart(TempDate), 'MMM-YY') as P_MonthYear, ApplyMap('QuartersMap', month(TempDate), Null()) as P_Quarter, Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as P_WeekYear, WeekDay(TempDate) as P_WeekDay
I have 2 other master calendars in my data model aswell, think these have something to do with the issue? it seems to not like my TempCalendar, but this is the exact same as my other 2 tempCalendars, so not sure what the issue is 😕