Qlik Community

Qlik Sense Deployment & Management

Discussion board where members learn more about Qlik Sense Installation, Deployment and Management.

jarcardoso
New Contributor

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;

1 Solution

Accepted Solutions
jaygarcia
New Contributor III

Re: Create Master Calendar from date column - Sense App

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

3 Replies
Digvijay_Singh
Honored Contributor III

Re: Create Master Calendar from date column - Sense App

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,

jarcardoso
New Contributor

Re: Create Master Calendar from date column - Sense App

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

jaygarcia
New Contributor III

Re: Create Master Calendar from date column - Sense App

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