How to Assign time buckets to a date-time field

[Anonymous]

Hi ,

i need help to create time bucket from a Date-Time field, time is in 24 hr Format , required buckets are as below, Pls help

TimeBucket

<08.30 AM

08.30AM-09.30AM

09.30AM-10.30AM

10.30AM-11.30AM

11.30AM-12.30PM

12.30PM-01.30PM

01.30PM-02.30PM

02.30PM-03.30PM

03.30PM-04.30PM

04.30PM-05.30PM

05.30PM-06.30PM

06.30PM-07.30PM

>7.30PM

WEEKEND (IF day is saturday/sunday)

Thanks In advance

[marcus_sommer]

How to assign time buckets from a datetime

[agustinbobba]

Hi!

Try this,

LOAD
     *,
     If(Minute >= 30, Hour & ':30 - ' & (Hour + 1) & ':30',
          (Hour - 2) & ':30 - ' & (Hour - 1) & ':30')           AS Range
     ;
     LOAD
          *,
          Hour(TimeStamp)      AS Hour,
          Minute(TimeStamp)    AS Minute;
LOAD
     TimeStamp#(TimeStamp, 'MM/DD/YYYY hh:mm:ss') AS TimeStamp
Resident Dates;

Best regards

Agustin

[Anonymous]

Hi,

I have used below script as a dimension, it worked for the time Greater equal to 12:30, but for the time below 12.30 PM it assigned "12.30PM-01.30PM" BUCKET, can anyone help to fix this.

= If(TIME(SUBMIT_DTTM,'hh:mm:ss') < ('08:30:00 AM') ,                                                   Dual('<08:30AM', 1),

If(TIME(SUBMIT_DTTM,'hh:mm:ss') >= ('08:30:00 AM') and TIME(SUBMIT_DTTM,'hh:mm:ss') < ('09:30:00 AM') , Dual('08:30AM-09:30AM', 2),

If(TIME(SUBMIT_DTTM,'hh:mm:ss') >= ('09:30:00 AM') and TIME(SUBMIT_DTTM,'hh:mm:ss') < ('10:30:00 AM') , Dual('09:30AM-10:30AM', 3),

If(TIME(SUBMIT_DTTM,'hh:mm:ss') >= ('10:30:00 AM') and TIME(SUBMIT_DTTM,'hh:mm:ss') < ('11:30:00 AM') , Dual('10:30AM-11:30AM', 4),

If(TIME(SUBMIT_DTTM,'hh:mm:ss') >= ('11:30:00 AM') and TIME(SUBMIT_DTTM,'hh:mm:ss') < ('12:30:00 PM') , Dual('11:30AM-12:30PM', 5),

If(TIME(SUBMIT_DTTM,'hh:mm:ss') >= ('12:30:00 PM') and TIME(SUBMIT_DTTM,'hh:mm:ss') < ('13:30:00 PM') , Dual('12:30PM-01:30PM', 6),

If(TIME(SUBMIT_DTTM,'hh:mm:ss') >= ('13:30:00 PM') and TIME(SUBMIT_DTTM,'hh:mm:ss') < ('14:30:00 PM') , Dual('01:30PM-02:30PM', 7),

If(TIME(SUBMIT_DTTM,'hh:mm:ss') >= ('14:30:00 PM') and TIME(SUBMIT_DTTM,'hh:mm:ss') < ('15:30:00 PM') , Dual('02:30PM-03:30PM', 8),

If(TIME(SUBMIT_DTTM,'hh:mm:ss') >= ('15:30:00 PM') and TIME(SUBMIT_DTTM,'hh:mm:ss') < ('16:30:00 PM') , Dual('03:30PM-04:30PM', 9),

If(TIME(SUBMIT_DTTM,'hh:mm:ss') >= ('16:30:00 PM') and TIME(SUBMIT_DTTM,'hh:mm:ss') < ('17:30:00 PM') , Dual('04:30PM-05:30PM', 10),

If(TIME(SUBMIT_DTTM,'hh:mm:ss') >= ('17:30:00 PM') and TIME(SUBMIT_DTTM,'hh:mm:ss') < ('18:30:00 PM') , Dual('05:30PM-06:30PM', 11),

If(TIME(SUBMIT_DTTM,'hh:mm:ss') >= ('18:30:00 PM') and TIME(SUBMIT_DTTM,'hh:mm:ss') < ('19:30:00 PM') , Dual('06:30PM-07:30PM', 12),

If(TIME(SUBMIT_DTTM,'hh:mm:ss') >= ('19:30:00 PM'),                                                      Dual('>=07:30PM', 13))))))))))))))

Regards,

Arjin.