topic Time Bucket for Hour in QlikView

Time Bucket for Hour

Thu, 07 Apr 2016 10:15:05 GMT

Hi,

I have a requirement to create time bucket like 8:30 to 9:30, 9:30 to 10:30,10:30 to 11:30......my date format is like 01/01/2016 4:30:10 AM. How to achieve this?

Thanks,

Re: Time Bucket for Hour

sunny_talwar — Thu, 07 Apr 2016 10:19:09 GMT

I would suggest creating a time field in your application

LOAD *,

If(Time >= Time#('08:30 AM', 'hh:mm TT') and Time < Time#('09:30 AM', 'hh:mm TT') , Dual('8:30 to 9:30', 1),

If(Time >= Time#('09:30 AM', 'hh:mm TT') and Time < Time#('10:30 AM', 'hh:mm TT') , Dual('9:30 to 10:30', 2),

If(Time >= Time#('10:30 AM', 'hh:mm TT') and Time < Time#('11:30 AM', 'hh:mm TT') , Dual('10:30 to 11:30', 3),

LOAD TimeStamp,

Time(Frac(TimeStamp)) as Time,

Date(Floor(TimeStamp)) as Date

....

Re: Time Bucket for Hour

Chanty4u — Thu, 07 Apr 2016 10:19:15 GMT

=if(Time='8:30 to 9.30' B1, if(Time=9.30 to 10.30 B2, 'B3')) As newBucket

Re: Time Bucket for Hour

Oleg_Troyansky — Thu, 07 Apr 2016 10:21:27 GMT

I'd use something like this for the bucket calculation:

=timestamp(ceil(MyTimeField, 1/24, 1/48))

- rounding the time up to the level of 1 hour (1/24), with the offset of 30 minutes (1/48)

cheers,

Oleg Troyansky

check out my new book QlikView Your Business!

Re: Time Bucket for Hour

jagan — Thu, 07 Apr 2016 10:55:37 GMT

Hi,

Try like this

LOAD

If(Minute >= 30, Hour & ':30 - ' & (Hour + 1) & ':30', (Hour - 2) & ':30 - ' & (Hour - 1) & ':30') AS TimeRange;

LOAD

Hour(TimeStamp) AS Hour,

Minute(TimeStamp) AS Minute;

LOAD

TimeStamp#(TimeStamp, 'MM/DD/YYYY hh:mm:ss TT') AS TimeStamp

INLINE [

TimeStamp

01/01/2016 1:30:10 AM

01/01/2016 4:30:10 AM

01/01/2016 5:30:10 AM

01/01/2016 5:45:10 AM

];

Regards,

Jagan.

Re: Time Bucket for Hour

jagan — Thu, 07 Apr 2016 10:57:39 GMT

Also check this link

Master Calendar with every 30 Minutes