3 Replies Latest reply: Feb 27, 2018 6:52 AM by Jose Cardoso RSS

    Create Master Calendar from date column - Sense App

    Jose Cardoso

      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;