Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
Is 15 - 120min a typo ?
Did you mean 0 - 120min ?
If so then the Class() function should be useful.
Not typo, the 1st interval is 15 - 120 min, thereafter 120 - 240 min and so on
How do you do it if the records are in the thousands?
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
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;
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?
Thousands of row of minutes data, the time column i pass on top is just a small part