topic Re: How to group mins into time interval in QlikView

How to group mins into time interval

Wed, 02 Dec 2015 16:41:50 GMT

For the time column

201

121

159

Need to group them into Time interval:

15 - 120min

120 - 240min

240 - 360min

each interval is 120min

Re: How to group mins into time interval

sunny_talwar — Wed, 02 Dec 2015 17:48:03 GMT

May be try this:

Table:

LOAD Min,

If(Num(Min) >= (0/(24*60)) and Num(Min) < (120/(24*60)), '15 - 120min',

If(Num(Min) > (120/(24*60)), '120 - 240min',

If(Num(Min) > (240/(24*60)), '240 - 360min'))) as Range;

LOAD Interval#(Min, 'mm') as Min;

LOAD * Inline [

Min

201

121

159

];

Re: How to group mins into time interval

Anonymous — Wed, 02 Dec 2015 17:51:45 GMT

Is 15 - 120min a typo ?

Did you mean 0 - 120min ?

If so then the Class() function should be useful.

Re: How to group mins into time interval

Thu, 03 Dec 2015 10:47:17 GMT

Not typo, the 1st interval is 15 - 120 min, thereafter 120 - 240 min and so on

Re: How to group mins into time interval

Thu, 03 Dec 2015 10:47:42 GMT

How do you do it if the records are in the thousands?

Re: How to group mins into time interval

Anonymous — Thu, 03 Dec 2015 11:04:52 GMT

You have mentioned 15-120 Mins...

What about where time is <=15 mins??

If you want to take it 15-120 mins

then try at script level like:

IF(TimeField>=15 and TimeField<=120,Dual('15-120 Min',1) //Dual is useful for sorting

IF(TimeField>120 and TimeField<=240,Dual('120-240 Min',2)

and so on

Else if you take it 0-120 min and interval is fixed at 120 min then as Bill suggested:

=class(TimeField,120) at front end

Re: How to group mins into time interval

Kushal_Chawda — Thu, 03 Dec 2015 11:14:06 GMT

try something like below. It will create the buckets from 15-120 then Class function will create the buckets for 120 min Interval

Data:

LOAD *, if(Min1='0 - 120 Min',Min2,Min1) as Buckets;

LOAD *, replace(Class(Min,120),'<= x <','-') &' Min' as Min1,

If(Min >= 15 and Min <= 120 , '15 - 120 Min') as Min2 Inline [

Min

201

121

159

];

DROP Fields Min1,Min2;

Re: How to group mins into time interval

sunny_talwar — Thu, 03 Dec 2015 12:00:43 GMT

Not sure what you mean when you say you have records in thousands? Do you have thousands of row of data or the minutes can be in thousands?

Re: How to group mins into time interval

Thu, 03 Dec 2015 12:46:50 GMT

Thousands of row of minutes data, the time column i pass on top is just a small part

Re: How to group mins into time interval

ganeshreddy — Thu, 03 Dec 2015 12:53:22 GMT

This may helps you .

Bucket Creation In List Box

Regards,

Ganesh

Re: How to group mins into time interval

sunny_talwar — Thu, 03 Dec 2015 14:42:09 GMT

I believe it should still work with thousands or million of rows of data. I am not sure I understand your concern.

Re: How to group mins into time interval

MarcoWedel — Fri, 04 Dec 2015 00:46:58 GMT

Hi,

another solution could be:

table1:
LOAD *,
    If(time<15,Dual('<15min',14),If(time>=15 and time<120,Dual('15 - 120min',15),Dual(Replace(Class(time,120),'<= x <','-')&'min',Class(time,120)))) as [time interval];
LOAD Floor(Rand()*500) as time
AutoGenerate 30;

hope this helps

regards

Marco

Re: How to group mins into time interval

Fri, 04 Dec 2015 02:34:38 GMT

I see there are 5 rows of 15 - 120 min, is there a way to group it together and replace the time column as 5 and a unique time interval as 15 - 120min?

Re: How to group mins into time interval

MarcoWedel — Fri, 04 Dec 2015 19:56:59 GMT

You mean like

count([time interval])

Yes, of course.

Regards

Marco

Re: How to group mins into time interval

MarcoWedel — Sat, 05 Dec 2015 01:19:22 GMT

example:

hope this helps

regards

Marco

Re: How to group mins into time interval

Sat, 05 Dec 2015 11:55:18 GMT

Is there a way to open your file? I'm using personal edition

Re: How to group mins into time interval

MarcoWedel — Sat, 05 Dec 2015 13:26:38 GMT

‌create new qvw, copy my script and paste it using the script editor.

reload, create a chart straight table using time interval as dimension and count([time interval]) as expression.

regards

Marco

Re: How to group mins into time interval

Sat, 05 Dec 2015 14:54:53 GMT

How come the count all total as 30?

Re: How to group mins into time interval

MarcoWedel — Sat, 05 Dec 2015 20:23:44 GMT

it's the sum

2+6+5+11+5+1 = 30

hope this helps

regards

Marco

Re: How to group mins into time interval

Sun, 06 Dec 2015 02:51:42 GMT

I was trying to achieve the above, as you notice the 2 boxes on the right all total 1000 (in your eg should be 30) and the time interval 15 - 120min is both 107?