How to group time buckets

[#1]

Dear Mates,

I have a requirement to plot a bar chart with dimension being time buckets and expression being the punching time (InTime) of set of students.I was able to create the time buckets and plot the graph,but I need to group it in another way such as there should be only 5 groups like before 8 am,8am-9am,9am-10am,10am-11am,after 11 am.The format of Intime is '00:00:00'.This is what I have done now to get each time buckets in script.

time(InTime,'h TT) & '-' & time(InTime+MakeTime(1,0,0),'h TT') as InTimeBucket.I tried with if conditions but no luck.So please help me to fine the best alternative possible.Thank you for your time.

Regards

Rishi

[swuehl]

Maybe something like

SET TimeFormat = 'hh:mm:ss';

LOAD InTime,

          If(InTime < Maketime(8), Dual('Before 8 am', 7),

              If(InTime >= Maketime(11), Dual('After 11 am',11),

                 Dual( time(InTime,'h TT) & '-' & time(InTime+MakeTime(1,0,0),'h TT'), Hour(InTime) )

               ))     as InTimeBucket,

...

[Anonymous · Accepted solution]

Hi Rishi,

You can do it with apply map:

HourGroup:

Mapping

LOAD * Inline [

    Hour, Group

    0,      Before 8 am

    1,      Before 8 am

    2,      Before 8 am

    3,      Before 8 am

    4,      Before 8 am

    5,      Before 8 am

    6,      Before 8 am

    7,      Before 8 am

    8,      8am-9am

    9,      9am-10am

    10,      10am-11am

];

On your table:

ApplyMap ('HourGroup',Hour(InTime),'After 11 am') as InTimeBucket

Regards!!!

[#4]

Thanks for the quick response,I tried both but I am getting only after 11 am

Then I tried with my earlier expression and chose to restrict max 4 values and got like the one below

[Anonymous]

Hi Rishi,

With apply map:

This is the code

HourGroup:

Mapping

LOAD * Inline [

    Hour, Group

    0,      Before 8 am

    1,      Before 8 am

    2,      Before 8 am

    3,      Before 8 am

    4,      Before 8 am

    5,      Before 8 am

    6,      Before 8 am

    7,      Before 8 am

    8,      8am-9am

    9,      9am-10am

    10,      10am-11am

];

Data:

LOAD *,

    ApplyMap ('HourGroup',Hour(InTime),'After 11 am') as InTimeBucket;

LOAD

Time(Rand())    as InTime

AutoGenerate 30;

Regards!

[#6]

It worked when I moved the mapping table before the fact table in script.Its working perfectly.Thank you for your time.

[Anonymous]

You're wellcome!!