Hi,
I'm creating a master calendar into my data model and got "Field 'a' not found" error.
I think the reason is that creating variables in the load script failed, because I'm not able to see the variables (varMinDate & varMaxDate) in the app's variables list.
Any idea how to solve this?
Here's my code:
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='#,##0.00€;-#,##0.00€';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=0;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET CreateSearchIndexOnReload=0;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';
[Data]:
LOAD
Source,
[Company code],
[Profit center code],
[Customer],
[Customer country],
Date#(Date(Date),'DD.MM.YYYY'))) AS Dateday,
[Revenue]
FROM [123];
TEMP:
LOAD
Min(Dateday) as minDate,
Date(YearEnd(Today()),'DD.MM.YYYY') as maxDate
RESIDENT DATA;
Let varMinDate= Num(Peek('minDate',0,'Temp');
Let varMaxDate= Num(Peek('maxDate',0,'Temp');
DROP TABLE TEMP;
TEMP_CALENDAR:
Load
$(varMinDate) + IterNo()-1 as Num,
Date($(varMinDate) + IterNo()-1) AS TempDate
AutoGenerate 1 While $(varMinDate) + IterNo()-1 <= $(varMaxDate);
MASTER_CALENDAR:
LOAD
TempDate AS Dateday,
DATE(MonthStart(TempDate),'YYYY-MM') AS MonthKey,
ApplyMap('QUARTERS_MAP',Month(TempDate),Null()) As Quarter,
Month(TempDate) AS Month,
Year(TempDate)&Num(Month(Tempdate),00) AS YearMonth,
Year(TempDate)&'-'&Num(Month(Tempdate),00) AS Period,
Year(TempDate)&'-'&ApplyMap('QUARTERS_MAP',Month(TempDate),Null()) AS YearQuarter
RESIDENT TEMP_CALENDAR
ORDER BY TempDate ASC;
DROP TABLE TEMP_CALENDAR;
Thanks!
