topic Create Master Calendar from date column - Sense App in Management & Governance

Create Master Calendar from date column - Sense App

Anonymous — Fri, 23 Feb 2018 15:12:22 GMT

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;

Re: Create Master Calendar from date column - Sense App

Digvijay_Singh — Fri, 23 Feb 2018 21:51:57 GMT

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,

Re: Create Master Calendar from date column - Sense App

Anonymous — Tue, 27 Feb 2018 11:52:29 GMT

Hi, thank you, but didn't work. And i need the timestamp available in the console table.

Re: Create Master Calendar from date column - Sense App

jaygarcia — Tue, 27 Feb 2018 16:22:39 GMT

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):

QlikView Date fields

Hope it helps.

Jay