Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Time Bucket for Hour

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,

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

5 Replies
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
MVP
MVP

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

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Luminary Alumni
Luminary Alumni

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
Luminary Alumni
Luminary Alumni