Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

24 Replies
sunny_talwar

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

24

201

11

43

21

57

0

121

22

4

159

0

2

46

];


Capture.PNG



Anonymous
Not applicable
Author

Is 15 - 120min a typo ?

Did you mean 0 - 120min ?

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

Not applicable
Author

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

Not applicable
Author

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

Anonymous
Not applicable
Author

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

Kushal_Chawda

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;

sunny_talwar

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?

Not applicable
Author

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

ganeshreddy
Creator III
Creator III

This may helps you  .

Bucket Creation In List Box

Regards,

Ganesh