The Master Time Table

[hic]

The Master Calendar table is a central component in many QlikView applications: It is a dimension table listing different calendar attributes such as Year, Month, Day, etc.

But what about time attributes, such as hours, minutes and seconds? How should these be handled? Should these also be included in the Master Calendar? Or should you create a Master Time table?

[hic]

 Often you should create a separate table for the Time dimension. To understand why, we need to look at the cardinality of the field used as key, i.e. the number of possible values. If a date is used as key, there can be at most 366 unique values per year. Hence, the Master Calendar will have at most 366 records per year. This makes the Master Calendar a small, efficient dimension table.

But if we instead use a timestamp as key, we have a different situation. A timestamp rounded to the nearest second will have over 30 million possible values per year. And if it has milliseconds too, it becomes even worse… A timestamp usually has almost the same number of unique values as the transaction table has records. It goes without saying that such a timestamp is inappropriate as dimension key. The dimension table would in many cases become just as big as the transaction table and nothing would be gained.

So, what should we instead do?

One good way is to convert the timestamp to two keys: Date and Time. The Date key needs to be truncated to the nearest integer so that no time information remains. For the Time key, it's the other way around: The integer part of the number needs to be removed so that no date information remains. In addition, it should be truncated - preferably to the nearest minute or 10-second interval - to keep the cardinality down.

These keys can be created through:

     Date(Floor(Timestamp)) as Date
     Time(Floor(Frac(Timestamp),1/24/60),'hh:mm') as Time

This way you will be able to have two master tables, one with 366 records per year, and one with perhaps 1440 records – both tables small and efficient. The Master Time table can have fields for hours, minutes and e.g. work shifts and can easily be created from the above key.

However, you lose information when you remove the information about seconds and milliseconds. So, you need to ask yourself whether you need this information or not. If you do, the best option is often to keep the original timestamp in the transaction table, in addition to the created keys, so that it can be used to calculate different measures. If not, just don’t load the original timestamp.

Summary:

• Think of the cardinality when you create dimensions.
• Make sure that the key to the Master Calendar table is an integer (formatted as a date): Use the Floor() function to remove the time component.
• If you need hour and minute as fields, create a Master Time table.
• Make sure that the key to the Master Time table is the fractional part of the timestamp, truncated to e.g. the nearest minute: Use a combination of the Floor() and the Frac() functions to create it.

HIC

[#3]

Ahhh - but why do you wanna divide it by 4 ? Then you get too many values dont you? If i remove the multiply and divide by 4 i get my exact numbers i want in my dimension.

But then my chart is showing almost all my data entries at the 23 hour (and it should not)

[hic]

My code will create one entry per 15-second interval. Then you need

     24 * 60 * 4

intervals per day. And the timestamp you can create by

     Time( RecNo() / 24 / 60 / 4 )

If all your data entries are around 23:00, then you have a numerical problem. Format the field as number and see what you get. A time should always be between 0 and 1.

HIC

[#5]

I see your point - Its good to make 15second intervals so you dont get a row for each second.

However i making this on Hour/minutes only - So i remove the 4. But still i get totally different results. If i use this method i get 407 values which hits null.

If i do like this instead though, i get all my values correct (but the strange thing is that it contains the excact same values as yours)

//MasterCalendarTime:

//Load distinct

//Time as Time,

//Hour(Time) as Hour,

//Minute(Time) as Minute

//

//RESIDENT Hej

//

//Order by Time ASC;

However i did notice that if i run this Time( RecNo() / 24 / 60) its shows the stamp as 20:01:00 where my TimeKey is 20:01 - Could this be an issue?

[hic]

Why you get values with NULL, I cannot say without seeing data.

The formatting - 20:01:00 or 20:01 - is just formatting. It should not matter. If you want to, you should use a formatting function:

     Time(Time,'hh:mm') as Time

HIC

[#7]

Okay, thank you for the help anyways Ill keep this going on in a forum post instead. Ill post a document in there you can see if it is Thanks

[isingh30]

Thank you Henric

[bbmmouha]

@thomas Jensen

hello

i have the same problem as you

do you have the solution

if it's the case may you share it

thank you

[sh1va]

Thanks @hic for creating this topic. I'm relatively new to QlikSense, and I want to create a "MasterTime Table" in the same fashion of a Master Calendar, as you suggest in this post.

I'm struggling in understanding how to iterate through Time periods in a similar way I iterate through Dates!
Here's the script I use for creating the MasterCalendar from a start date to and end date, though iterating through them.

LET vMinDate = num('2020-08-05');
LET vMaxDate = num('2023-01-18');

MasterCalendar:											
Load
    Num(Year(DateNum),'0000') & Num(Month(DateNum),'00') & Num(Day(DateNum),'00') as DateKey,
    date(DateNum,'YYYY-MM-DD') as DisplayDate,				
    Date(DateNum,'WWWW') as Day, 							
    Date(DateNum,'WWW') as DayAbbr,							
    Week(Date(DateNum)) as YearWeekNbr, 					
    Date(DateNum,'MMMM') as Month,							
    Month(DateNum) as MonthAbbr,							
    Num(Month(DateNum)) as MonthNbr, 						
    Year(DateNum) as YearNum,								
    Year(DateNum) &  Num(Month(DateNum)) as YearMonth; 
  Load																	                                                      
    $(vMinDate) + (IterNo()) as DateNum					    
  AutoGenerate 1										
  While 												
    $(vMinDate) + (IterNo()) <= $(vMaxDate);
store * from MasterCalendar into 'lib:etc/etc/path.qvd' (QVD) ;

Since my dataset has multiple entries for the same day, I expected to work in the same fashion in order to set a MasterTime Table where store a numeric ID "TimeKey" for each second (actually minute could be fine as well in order to optimize performances). 
To be honest I'm not entirely sure this is a correct approach. I'm not even sure how to link the masterCalendar to the TimeTable right now... I could use some help in this way as well.

In any case, I tryed many things, always getting empty tables, errors and stuff...
Look, I don't even know how to report all my tries.. Here's an (obviously) incorrect code example 

LET vMinTime =  num('00:00:00');
LET vMaxTime = num('23:59:59');

MasterTime:
Load
    right(Num(IterNo(), '000000'),6) as TimeKey,
    Time(IterNo(), 'hh:mm:ss') as DisplayTime,
    Hour(Time(IterNo(), 'hh:mm:ss')) as Hour,
    Minute(Time(IterNo(), 'hh:mm:ss')) as Minute,
    Second(Time(IterNo(), 'hh:mm:ss')) as Second;
Load
    $(vMinTime) + (IterNo()) as TimeNum
AutoGenerate 1
  While 													
  $(vMinTime) + (IterNo()) <= $(vMaxTime);

 
Any help in understanding the script issues and/or the modelling strategy mistakes would be really appreciated.
Kind Regards

[marcus_sommer]

The master-calendar and the master-timetable won't be connected to each other else each one is linked to the fact-table per the fields date respectively time which are separate fields there.

Beside this you don't need the while-loop within the timetable-load else autogenerate would be more suitable, like:

t:
load *, hour(Time) as Hour, ... more fields;
load time(1/24/60/60 * (recno() - 1)) as Time autogenerate 86400;

The same creation-logic could be applied to the master-calendar, too.

Further there are multiple issues within your loads, like:

• formatting within second/minute/hour() which are superfluous
• using a formatting to convert a value like date(DateNum, 'MMM') as Month because it remains a date
• using string-functions respectively string-concatenation to create a key-field - of course it's possible but this could be reached with a formatting, like: time(Time, 'hhmmss') or date(Date, 'YYYYMMDD') respectively should it really be a 6/8 digit numeric looking value it could be done with numeric operations, like: year(Date) * 10000 + month(Date) * 100 + day(Date)