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
Pasting it's content here
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
LOAD Date(Floor(TimeStamp)) AS Date,
Hour(TimeStamp) AS Hour;
Timestamp($(vMinDate) + (RecNo() - 1)/24) as TimeStamp
AUTOGENERATE 24 * $(vDays);
Note : 24 = 1 Day (24 Hours)
Hope this helps.
jagan it seems that not all can view this document unless they are part of Qlik India group.
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.
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