
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Create Master Calendar from date column - Sense App
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;
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Jose
I think you need to change the one on your "generic_message_log" table for this line:
Date(floor([run_time]),'DD-MMM-YYYY') AS [Date],
You don't need to use "floor" in the one in the Calendar table as it doesn't have 'time'.
I'd suggest you have a look into this useful PDF, specially Tip 4 (page 5):
Hope it helps.
Jay

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you remove time part from your date and then check. I am suspecting having numeric part like123456.2345 (with decimals), might be not allowing exact match while making association.
Date(Floor(TempDate), 'DD-MMM-YYYY') AS Date,

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, thank you, but didn't work. And i need the timestamp available in the console table.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Jose
I think you need to change the one on your "generic_message_log" table for this line:
Date(floor([run_time]),'DD-MMM-YYYY') AS [Date],
You don't need to use "floor" in the one in the Calendar table as it doesn't have 'time'.
I'd suggest you have a look into this useful PDF, specially Tip 4 (page 5):
Hope it helps.
Jay
