It has been 7 years since the QlikView for Developer's Cookbook was published!
The book, which was very much targeted at QlikView developers, is still available. Just for fun, I thought that it might be interesting to write a series of posts looking back at some of those recipes and seeing how they could be recreated in Qlik Sense Business.
In this post, I'll look at the recipe on Consolidating a date-time value into quarter hourly segments.
The premise behind this recipe is one that is relatively straightforward, we would like to round down Time Stamp data to a less unique value. The best reason for doing this is to simply remove the uniqueness of the data - Time Stamps could represent millions of values in a day, because they can be sub-second, when there is really only 86400 seconds in one day, 1440 minutes and 96 quarters. The less unique your data, the better that Qlik can store it. Of course, you need to bear in mind the business need for the presentation of the values - if they don't need minutes and seconds, don't store them!
The simple load statement for this recipe is:
TimeCounter,
TimeStamp(TimeStamp) as Date,
WeekDay(TimeStamp) As WeekDay,
TimeStamp(Round(TimeStamp, (1/(24))), 'M/D/YY HH:mm') As Hour,
TimeStamp(Round(TimeStamp, (1/(24*4))), 'M/D/YY HH:mm')
As QuarterHour;
Load
Today() + (Rand()) As TimeStamp,
1 as TimeCounter
AutoGenerate(1000);
This does give us a result which may be good for our purposes. However, we need to bear in mind that this is a mathematical rounding, and that may not match business rules that we have to abide by. In this example, we can see that the 06:00:00 hour covers time slots from 05:30:00 to 06:29:59. The 06:15:00 quarter covers times slots from 06:07:31 to 06:22:29. These ranges may not be what a business user would expect - would you expect 5:30am to appear in the 6am group?
If you want your 06:00 to be 06:00:00 to 06:59:59, then this might work:
TimeStamp#(Text(TimeStamp(TimeStamp, 'YYYYMMDD HH'))&':00','YYYYMMDD HH:mm') As Hour
As well as holding a Master's Degree in Data Analytics, Stephen Redmond is a practicing Data Professional of over 20 years experience. He is author of Mastering QlikView, QlikView for Developer's Cookbook and QlikView Server and Publisher.
Follow @stephencredmond
