topic Re: Add time calendar to master calendar in QlikView

Add time calendar to master calendar

GuillaumeRUE — Tue, 06 Aug 2019 13:34:00 GMT

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?

Re: Add time calendar to master calendar

Nadi_Clarke — Wed, 07 Aug 2019 11:47:45 GMT

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

Re: Add time calendar to master calendar

GuillaumeRUE — Wed, 07 Aug 2019 11:57:14 GMT

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?

Re: Add time calendar to master calendar

marcus_sommer — Wed, 07 Aug 2019 15:36:57 GMT

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

Re: Add time calendar to master calendar

Brett_Bleess — Mon, 26 Aug 2019 13:29:39 GMT

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