Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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

Re: How to group mins into time interval

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



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.

Highlighted
Not applicable

Re: How to group mins into time interval

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

Not applicable

Re: How to group mins into time interval

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

balrajahlawat
Esteemed Contributor

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;

MVP
MVP

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?

Not applicable

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

ganeshreddy
Contributor III

Re: How to group mins into time interval

This may helps you  .

Bucket Creation In List Box

Regards,

Ganesh