

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Master Calendar to Hours Issues - world of pain :)
Hi Guys,
I can't share any data sorry but happy to share script - I was hoping to get some help on the auto generate section in my master calendar. I have concatenated many of my data sources but i'm now trying to link it to a master calendar. I can do this down to Day but my master calendar isn't generating hours
I am trying to link my data table to the master calendar on [DateHour] which is created and in the format of DD/MM/YYYY hh. I did this by concatenating a Date and hour function on a DateTime field. The Master calendar script is simple standard daily one from the Qlik help videos lol
I think i might be a bit confused - it looks like my min and max date in Temp table are also not in the right format.
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min([DateHour]) as minDate,
max([DateHour]) as maxDate
Resident Data;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
Date(TimeStamp(Floor(TempDate)), 'DD/MM/YYYY') As Date,
Date(Date(TempDate, 'DD/MM/YYYY hh:mm:ss:fff'), 'DD/MM/YYYY hh') AS [DateHour],
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Dual(Date(TempDate, 'MMM-YY'), MonthEnd(TempDate)) AS MonthName,
Day(TempDate) As Day,
ApplyMap('QuartersMap', month(TempDate), Null()) As Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Pasting it's content here
Hi All,
Please find below script for generating the Master Calendar with Hours.
LET vMinDate = Num(MakeDate(2014,1,1)); // Calendar Start Date
LET vMaxDate = Num(Today()); // Calendar End Date
LET vDays = vMaxDate - vMinDate + 2; // Calculating number of days between Start & End Dates
CalendarTemp:
LOAD Date(Floor(TimeStamp)) AS Date,
TimeStamp,
Hour(TimeStamp) AS Hour;
LOAD
Timestamp($(vMinDate) + (RecNo() - 1)/24) as TimeStamp
AUTOGENERATE 24 * $(vDays);
Note : 24 = 1 Day (24 Hours)
Hope this helps.
Regards,
Jagan.
jagan it seems that not all can view this document unless they are part of Qlik India group.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This script is only generating dates, not hours within the date... so for example, if you you fact table you have a record with a DateHour of 02/22/2018 20, then it won't be associated with anything in your calendar because calendar is only creating everything with 0 hours. (02/20/2018 00, 02/21/2018 00, 02/22/2018 00).
Two ways to handle this
1) Create a The Master Time Table
2) Create a master calendar with hour Master Calendar with Hour

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sometimes as well as a Master Calendar I also create a Master Clock at whatever granularity is needed, be it hour, minute or second.
This massively reduces the number of rows in one's Master Calendar if it was holding times at seconds granularity. It reduces the number of unique keys give a performance benefit.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Sunny - I am not able to access the 'master Calendar with Hour' Because it's part of a Qlik India group and the link just tries to take me there.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I've not heard of a master clock before. I'll have a google and see if I can find out more about it and how it works etc
Thanks,
Alex

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Pasting it's content here
Hi All,
Please find below script for generating the Master Calendar with Hours.
LET vMinDate = Num(MakeDate(2014,1,1)); // Calendar Start Date
LET vMaxDate = Num(Today()); // Calendar End Date
LET vDays = vMaxDate - vMinDate + 2; // Calculating number of days between Start & End Dates
CalendarTemp:
LOAD Date(Floor(TimeStamp)) AS Date,
TimeStamp,
Hour(TimeStamp) AS Hour;
LOAD
Timestamp($(vMinDate) + (RecNo() - 1)/24) as TimeStamp
AUTOGENERATE 24 * $(vDays);
Note : 24 = 1 Day (24 Hours)
Hope this helps.
Regards,
Jagan.
jagan it seems that not all can view this document unless they are part of Qlik India group.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
HIC calls it The Master Time Table

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others
