Qlik Community

Qlik Sense Deployment & Management

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

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
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
Highlighted
Contributor III
Contributor III

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

View solution in original post

3 Replies
Highlighted
Master III
Master III

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,

Highlighted
Contributor
Contributor

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

Highlighted
Contributor III
Contributor III

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

View solution in original post