24 Replies Latest reply: Dec 6, 2015 5:22 PM by Marco Wedel

# How to group mins into time interval

For the time column

24

201

11

43

21

57

0

121

22

4

159

0

2

46

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

May be try this:

Table:

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

24

201

11

43

21

57

0

121

22

4

159

0

2

46

];

• ###### Re: How to group mins into time interval

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

• ###### Re: How to group mins into time interval

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

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

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

Hi Try to use class functon

It might solve your issue.

• ###### Re: How to group mins into time interval

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

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

• ###### Re: How to group mins into time interval

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

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

24

201

11

43

21

57

0

121

22

4

159

0

2

46

];

DROP Fields Min1,Min2;

• ###### Re: How to group mins into time interval

This may helps you  .

Bucket Creation In List Box

Regards,

Ganesh

• ###### Re: How to group mins into time interval

Hi,

another solution could be:

```table1:
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

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

You mean like

count([time interval])

?

Yes, of course.

Regards

Marco

• ###### Re: How to group mins into time interval

example:

hope this helps

regards

Marco

• ###### Re: How to group mins into time interval

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

• ###### Re: How to group mins into time interval

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

How come the count all total as 30?

• ###### Re: How to group mins into time interval

it's the sum

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

hope this helps

regards

Marco

• ###### Re: How to group mins into time interval

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?

• ###### Re: How to group mins into time interval

there you have to observe two things.

1.3rd column it is showing two 107 val right accoding to this value the range will show.

2.You have two airlines for same record ,thats the reason it is showing

-Nagarjna

• ###### Re: How to group mins into time interval

Hi,

please close your thread if your initial question regarding interval groups is answered and open a new thread for your aggregation questions. A sample application might help to understand your data model and aggregation issues.

thanks

regards

Marco

• ###### Re: How to group mins into time interval

Hi Wil,

If your range is same for all the time ,You can also use class function.

Try this:

Class(FieldName,120)

its gives you 120 range

-Nagarjuna

• ###### Re: How to group mins into time interval

Like this ?

PFA