
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!
One comment