Master Calendar

[Vikash938]

I have dataset and in the dataset date column available and format is DD/MM/YYYY.
Question is Based on that i want to create an master calendar. How to create in data load editor?

[rafaelencinas · Accepted solution]

Hi @Vikash938 

Try this script:

// Load the minimum and maximum dates from the data table
TempCalendar:
LOAD
Min(myDataColumn) AS MinDate,
Max(myDataColumn) AS MaxDate
RESIDENT MyData;

// Create variables for min and max dates
LET vMinDate = Peek('MinDate', 0, 'TempCalendar');
LET vMaxDate = Peek('MaxDate', 0, 'TempCalendar');

// Drop temporary table
DROP TABLE TempCalendar;

// Generate the Master Calendar using an AutoNumber loop
MasterCalendar:
LOAD
Date($(vMinDate) + IterNo() - 1) AS CalendarDate,
Year(Date($(vMinDate) + IterNo() - 1)) AS Year,
Month(Date($(vMinDate) + IterNo() - 1)) AS Month,
Day(Date($(vMinDate) + IterNo() - 1)) AS Day,
Week(Date($(vMinDate) + IterNo() - 1)) AS Week,
'Q' & Ceil(Month(Date($(vMinDate) + IterNo() - 1))/3) AS Quarter,
MonthName(Date($(vMinDate) + IterNo() - 1)) AS MonthYear,
WeekYear(Date($(vMinDate) + IterNo() - 1)) AS WeekYear,
Date($(vMinDate) + IterNo() - 1, 'YYYY-MM-DD') AS DateKey
AUTOGENERATE 1
WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

[Vikash938]

Hi @rafaelencinas 
Thanks for sharing.....