Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
hic
Former Employee
Former Employee

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?

 

 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.

Time cardinality per year.png

 

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?

Time cardinality per day.png

 

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

19 Comments