How to group time data into buckets in QlikView?

Question

I have a list of times in QlikView. For example:

1:45 am
2:34 am
3:55 am 
etc.

How do I split it into groups like this:

1 - 2 am
2 - 3 am
4 - 5 am
etc.

I used the class function, but something is wrong. It works but it doesn't create time buckets, it creates some sort of converted decimal buckets.

Really appreciate it. Thanks a lot. Actually I wanted to create a bucket list box where I can alter the bucket range by defining a variable and adding a slider. It works well for numeric data. But when it comes to time it's a bit complicated. This is my actual requirement. But yours was a great alternative as well. Cheers — Archid, Mar 10 '15 at 18:48

score 2 · Answer 1 · answered Mar 10 '15 at 10:22

You have a couple of options, by far the simplest would be to create a new field which reformats your time field, for example I created TimeBucket which formats the time field into hours, and appends this with the same time but with an hour added for the upper bound:

LOAD
    TimeField,
    Time(TimeField,'h tt') & ' - ' & Time(TimeField + maketime(1,0,0),'h tt') as TimeBucket;
LOAD 
    *
INLINE [
    TimeField
    1:45
    2:34
    3:55
    16:45
    17:56
];

This then results in the following:

Results of creating a custom field

However, depending on your exact requirements, this solution may have problems due to the nature of Time as this is a dual function.

Another alternative is to use intervalmatch as follows. One point to remember is that intervalmatch includes the end-points in an interval. This means for time, we have to make the "end" times be one second before the start of the next interval, otherwise we will generate two records instead of one if your source data has a time that sits on an interval boundary.

TimeBuckets:
LOAD
    maketime(RecNo()-1,0,0) as Start,
    maketime(RecNo()-1,59,59) as End,
    trim(mid(time(maketime(RecNo()-1),'h tt'),1,2)) & ' - ' & trim(time(maketime(mod(RecNo(),24)),'h tt')) as Bucket
AUTOGENERATE(24);

SourceData:
LOAD
    *
INLINE [
    TimeField
    1:45
    2:34
    3:55
    16:45
    17:56
];

BucketedSourceData:
INTERVALMATCH (TimeField)
LOAD 
    Start,
    End
RESIDENT TimeBuckets;

LEFT JOIN (BucketedSourceData)
LOAD
    *
RESIDENT TimeBuckets;

DROP TABLES SourceData, TimeBuckets;

This then results in the following:

The results of using intervalmatch

More information on intervalmatch may be found in both the QlikView installed help as well as the QlikView Reference manual.

score 0 · Answer 2 · answered Mar 09 '15 at 17:45

Write a nested if statement in your script:

If(TIME>1:45,'bucket 1', If(TIME>2:45,'bucket 2','Others' ) )

Not the most elegant, but if you can't get the 1:45 to work with the date() function, you can always convert to military time and just add the hours and minutes, then make buckets out of that.

How to group time data into buckets in QlikView?

2 Answers2