Add time calendar to master calendar

[GuillaumeRUE]

Hello,

I would like to add a time calendar to an existing QV document to allow people to filter on hours and minutes.

I found this subject : The Master Time Calendar but I have a problem when I try to create the time calendar.

For my master calendar I use this  script which works correctly for me:

varMinDate = num(date('2019-01-01 00:00','YYYY-MM-DD hh:mm'));
varMaxDate = num(date(today(),'YYYY-MM-DD hh:mm'));

// Creating a Temporary Calendar
TempCalendar:  
    LOAD  
       Date($(varMinDate) + IterNo() - 1) 	as TempDate  
       AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);  
   
MasterCalendar:  
Load               
date(TempDate,'DD/MM/YYYY')	as Date,	
day(TempDate) 			as Day,
year(TempDate) 			as Year,
Ceil(Month(TempDate)/3)		as QuarterNumber,
month(TempDate) 		as Month,                   
week(TempDate) 			as Week                
Resident TempCalendar  
Order By TempDate ASC;  
Drop Table 	TempCalendar; 

so I tried to modify it for my time calendar :

TempCalendarTime:
	LOAD  
       Time($(varMinDate)/24/60) 	as TempDate  
       AutoGenerate 1440 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

TIMECALENDAR:
LOAD
date(TempDate,'DD/MM/YYYY')	as Date,
Hour(TempDate)			as Hour,
Minute(TempDate)		as Minute
RESIDENT TempCalendarTime
Order By TempDate ASC;
DROP TABLE TempCalendarTime;

 But I only get 0 as hour and minute I and really don't understand why..

I hope I'm clear enough.

Can someone help me?

[Nadi_Clarke]

Why did you want to create to 2 separated tables for dates and times.

To be honest, I don't like your table TIMECALENDAR, because you will have 24 * 356 rows in this table per 1 year

[GuillaumeRUE]

At this moment, I can filter my data thanks to a Date (Year, month, day) but I get timestamps from DB and I would like to filter data with the time too (hour, minute, seconds if it's not too big).

So I tried to create a time table as I saw in the article I linked in the post to do it. But maybe only one calendar with date and time is enough?

[marcus_sommer]

A master-calendar may be depending on the available dates from the fact-table but a master time-table is usually independent from it and could be created with something like:

TimeTable:
load *, hour(Time) as Hour, minute(Time) as Minute;
load time(recno() / 24/60/60) as Time autogenerate 86400;

You may need some kind of rounding - with floor/ceil - within the TimeTable and your fact-table to ensure that both values really match because Qlik used a binary number-system which didn't always match to a decimal-system.

This means further that you split the timestamp into a date- and a time-field to connect both dimension-tables with the fact-table.

- Marcus

[Brett_Bleess]

You found the Master Calendar post, but you missed this one, which I suspect is the one you wanted, and Marcus' comment was trying to point you here as well I believe, hopefully this may do the trick.

https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Time-Table/ba-p/1469392

Regards,
Brett