Hi,
i have a table (generic_message_log) from where i want to take the column Date to create a Master Calendar of all dates in the column. Both tables are below. Question is that, when i'm adding fields as Master Items, like Year, Month,Week to create the Period (as per the Tutorials) the selection doesn't get updated. How can i get both tables linked on the Date column? (they are linked in the Data Model Viewer on Date field).
//generic_message_log
[generic_message_log]:
LOAD
[file_id] AS [m_file_id],
Date([run_time],'DD-MMM-YYYY hh:mm:ss') AS [Date],
[filename] AS [m_filename],
[run_time] AS [m_run_time];
SELECT "file_id",
"filename",
"run_time";
FROM "ftp_notifier"."generic_message_log";
//Master Calendar
LET varMinDate = NUM(PEEK('Date',0,'generic_message_log'));
LET varMaxDate = NUM(PEEK('Date',-1,'generic_message_log'));
TempCalendar:
LOAD
DATE($(varMinDate) + ROWNO() -1) AS TempDate
AUTOGENERATE $(varMaxDate) - $(varMinDate) +1;
// Create the Master Calendar
MasterCalendar:
LOAD
DATE(TempDate, 'DD-MMM-YYYY hh:mm:ss') AS Date,
WEEK(TempDate) AS Week,
YEAR(TempDate) AS Year,
MONTH(TempDate) AS Month,
DAY(TempDate) AS Day,
DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS MonthYear,
'Q' & CEIL(MONTH(TempDate)/3)&'-'&YEAR(TempDate) AS QuarterYear,
'W' & WEEK(TempDate)&'-'&YEAR(TempDate) AS WeekYear,
WEEKDAY(TempDate) AS WeekDay
RESIDENT TempCalendar
Order BY TempDate ASC;
DROP FIELD TempDate;
