topic Re: How to group time buckets in QlikView

How to group time buckets

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to group time buckets

swuehl — Tue, 21 Jun 2016 14:22:07 GMT

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,

...

Re: How to group time buckets

Anonymous — Tue, 21 Jun 2016 14:36:08 GMT

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!!!

Re: How to group time buckets

Wed, 22 Jun 2016 05:12:10 GMT

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

Re: How to group time buckets

Anonymous — Wed, 22 Jun 2016 06:45:16 GMT

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!

Re: How to group time buckets

Wed, 22 Jun 2016 06:52:33 GMT

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

Re: How to group time buckets

Anonymous — Wed, 22 Jun 2016 09:19:23 GMT

You're wellcome!!