Master Calendar to Hours Issues - world of pain :)

[alextomlins]

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;

[sunny_talwar]

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

[Anonymous]

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.

[alextomlins]

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.

[alextomlins]

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

[sunny_talwar · Accepted solution]

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.

[Anonymous]

HIC calls it The Master Time Table

[pablolabbe]

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