Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all 🙂
I am struggling with the master calendar...
I have implemented the following code:
MasterCalendar:
Load
TempDate AS DateKey,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
//Date#((Date(monthstart(TempDate), 'YYYYMM')),'YYYYMM') As YearMonth,
Year(TempDate)&date(TempDate,'MM') As YearMonth,
Date(MonthStart(TempDate), 'YYYYMM') as YearMonth2,
// Date(monthstart(TempDate), 'YYYYMMDD') As YearMonth,
WeekDay(TempDate) as WeekDay;
//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;
//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('DateKey', recno()))-1 as mindate,
//'20170101' as mindate,
max(FieldValue('DateKey', recno())) as maxdate
// '20180101' as maxdate
AUTOGENERATE FieldValueCount('DateKey');
but I got the following error message:
Not sure, but this could be your DateKey field format... I did this... and it worked for me
LOAD * INLINE [ DateKey 20170101 20180101 ]; MasterCalendar: LOAD TempDate AS DateKey, week(TempDate) As Week, Year(TempDate) As Year, Month(TempDate) As Month, Day(TempDate) As Day, 'Q' & ceil(month(TempDate) / 3) AS Quarter, Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, //Date#((Date(monthstart(TempDate), 'YYYYMM')),'YYYYMM') As YearMonth, Year(TempDate)&date(TempDate,'MM') As YearMonth, Date(MonthStart(TempDate), 'YYYYMM') as YearMonth2, // Date(monthstart(TempDate), 'YYYYMMDD') As YearMonth, WeekDay(TempDate) as WeekDay; //=== Generate a temp table of dates === LOAD Date(mindate + IterNo()) AS TempDate, maxdate // Used in InYearToDate() above, but not kept While mindate + IterNo() <= maxdate; //=== Get min/max dates from Field ===/ LOAD Min(Date#(FieldValue('DateKey', RecNo()), 'YYYYMMDD'))-1 as mindate, //'20170101' as mindate, Max(Date#(FieldValue('DateKey', RecNo()), 'YYYYMMDD')) as maxdate // '20180101' as maxdate AutoGenerate FieldValueCount('DateKey');
Not sure, but this could be your DateKey field format... I did this... and it worked for me
LOAD * INLINE [ DateKey 20170101 20180101 ]; MasterCalendar: LOAD TempDate AS DateKey, week(TempDate) As Week, Year(TempDate) As Year, Month(TempDate) As Month, Day(TempDate) As Day, 'Q' & ceil(month(TempDate) / 3) AS Quarter, Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, //Date#((Date(monthstart(TempDate), 'YYYYMM')),'YYYYMM') As YearMonth, Year(TempDate)&date(TempDate,'MM') As YearMonth, Date(MonthStart(TempDate), 'YYYYMM') as YearMonth2, // Date(monthstart(TempDate), 'YYYYMMDD') As YearMonth, WeekDay(TempDate) as WeekDay; //=== Generate a temp table of dates === LOAD Date(mindate + IterNo()) AS TempDate, maxdate // Used in InYearToDate() above, but not kept While mindate + IterNo() <= maxdate; //=== Get min/max dates from Field ===/ LOAD Min(Date#(FieldValue('DateKey', RecNo()), 'YYYYMMDD'))-1 as mindate, //'20170101' as mindate, Max(Date#(FieldValue('DateKey', RecNo()), 'YYYYMMDD')) as maxdate // '20180101' as maxdate AutoGenerate FieldValueCount('DateKey');
Hallo Sunny,
I already found out why it did not work 🙂
Thank you for your support.
Best regards,
Edi
The code I have posted now works (I assume the one you have posted also would work)
My mistake was the order of the script. I had sth like this:
Main
Calendar
Hierarchy
Exit Script
Extract
Facts
I thought that since I already ran before the part below "Exit Script" it would works, however to create the Master Calendar the "Fact" part should be place before the "Calendar" part:
Main
Facts
Calendar
Hierarchy
Exit Script
Extract