Time Bucket for Hour

[#1]

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,

[sunny_talwar]

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

          ....

[Chanty4u]

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

[Oleg_Troyansky]

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!

[jagan · Accepted solution]

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.

[jagan]

Also check this link

Master Calendar with every 30 Minutes