Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
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
Digvijay_Singh

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,

Anonymous
Not applicable
Author

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

jaygarcia
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