Working with your time dimension

Time……….. We never have enough of it and when we do we only get one chance to spend it. Next to that time is one of our primary dimensions used throughout most  QlikView applications. Building consistency and creating reusability into your workflow can be very convenient.

In most datawarehouses you’ll find a time table that is created or by views or dedicated tables for using this dimension. QlikView is able to generate a time dimension based on scripting. This scripting possibility can come in handy for a lot of different types of data generation, which will be covered somewhere in the future.

Getting my fellow QlikView addicts to work more productive would be nice ;). Therefore I thought it would be effective to share the following script:

LET vMinDateCal = num(Makedate(2001,01,01));
LET vMaxDateCal = num(Makedate(2013,12,31));

Temp:
LOAD 
Date($(vMinDateCal) + RowNo() - 1) AS TempDate  
AUTOGENERATE 1  
WHILE $(vMinDateCal) + IterNo()-1 <= $(vMaxDateCal); 

Time:
LOAD 
Date(TempDate,'DD-MM-YYYY') as Date_ID,
Monthname(TempDate) as MonthNameOri,
Date(MonthStart(TempDate), 'MMM') as MonthName, // Thanks to Barry Harmsen
Left(Weekday(Date(TempDate)),2)&' : '&Date(TempDate,'DD-MM-YY') as DateDay,
Left(Weekday(Date(TempDate)),2)&' : '&Date(TempDate,'DD-MM') as DateDayShort,
Year(TempDate)&'-'&Week(TempDate) as Week,
Rowno() as DayID,
Date(TempDate,'MM-DD-YYYY') as Date,
Week(TempDate) as tWeek,
Weekday(TempDate) as Weekday,
Left(Weekday(TempDate),2) as DayAbbr,
Day(TempDate) as Day,
Month(TempDate) as tMonth,
ceil(Month(TempDate)/3) as Quarter,
Year(TempDate)&'-Q'&ceil(Month(TempDate)/3) as QuarterID,
Year(TempDate)&'-'&Month(TempDate) as Month,
Year(TempDate)+1&'-'&Month(TempDate) as WhatIfMonth,
autonumber(Year(TempDate)&'-'&Month(TempDate)) as MonthID,
Year(TempDate) as Year
RESIDENT Temp;

Drop table Temp;

Now you could copy and paste this script into your script editor (CTRL-E) in QlikView. Reloading (CTRL-R) your application will add the data generated by this script into a table called ‘time’.

Script includes

You could also consider adding this script into an empty text file and include it in your script with script includes. Save your script file as ‘Time.txt’ and include it in the script with:

$(Include = Time.txt);

This post should help with being more productive and consistent towards your next QlikView app.

Good luck!