topic Re: group time intervals into time range in QlikView

group time intervals into time range

Mon, 19 May 2014 02:39:01 GMT

Hi, I want to group the time intervals into time range and this is what I scripted:

load *,if(timediff<30,'<30',

if(timediff>=30 and timediff <60,'<60',

if(timediff>=60 and timediff <90,'<90',

if(timediff>=90 and timediff <120,'<120',

if(timediff>=120 and timediff <150,'<150',

if(timediff>=150 and timediff <180,'<180',

if(timediff>=180 and timediff <210,'<210',

if(timediff>=210 and timediff <240,'<240',

if(timediff>=240 and timediff <270,'<270',

if(timediff>=270 and timediff <300,'<300',

if(timediff>=300 and timediff <330,'<330',

if(timediff>=330 and timediff <360,'<360')))))))))))) as timeinterval;

timediff contains the text type for the time intervals in minutes.

The result I got in the timediff listbox is only until '<90' when I'm supposed to get til '<360'. Thanks.

Re: group time intervals into time range

nagaiank — Mon, 19 May 2014 03:28:22 GMT

You may use the Class() function, e.g.

(timediff,30,'x') as [time range]

Re: group time intervals into time range

Anonymous — Mon, 19 May 2014 04:28:38 GMT

You may use qlikview inline load

LOAD * Inline [

min_value,max_value,Result

0,29,'<30'

30,59,'<60'

60,89,'<90'

90,119,'<120'

120,149,'<150'

150,179,'<180'

180,209,'<210'

210,239,'<240'

240,269,'<270'

270,299,'<300'

300,329,'<330'

330,359,'<360'

];

and can use Intervalmatch lookup function to find matches if you are loading from qlikview source like database table,Files or QVD.

Re: group time intervals into time range

Mon, 19 May 2014 05:14:04 GMT

How can I specifiy range in load inline? Cause' the above doesn't seem to work as it doesn't call the timediff field. Thanks.

Re: group time intervals into time range

Anonymous — Mon, 19 May 2014 05:19:54 GMT

Please go through following link.It will help you to understand how to create interval and use it in qlikview script

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

Re: group time intervals into time range

Anonymous — Mon, 19 May 2014 05:21:56 GMT

Use Class functions as it is used to define intervals:

Class(timediff,30,'x')

Re: group time intervals into time range

nizamsha — Mon, 19 May 2014 05:47:23 GMT

Here u got the one u want

LOAD * ,if(

TimeDiff<=30,Dual('<30',1),

if(TimeDiff>30 and TimeDiff<=60,Dual('>30- <=60',2),

if(TimeDiff>60 and TimeDiff<=90,Dual('>60- <=90',3),

if(TimeDiff>90 and TimeDiff<=120,Dual('>90- <=120',4),

if(TimeDiff>120 and TimeDiff<=150,Dual('>120- <=150',5),

if(TimeDiff>150 and TimeDiff<=180,Dual('>150- <=180',6),

if(TimeDiff>180 and TimeDiff<=210,Dual('>180- <=210',7),

if(TimeDiff>210 and TimeDiff<=240,Dual('>210- <=240',8),

if(TimeDiff>240 and TimeDiff<=270,Dual('>240- <=270',9),

if(TimeDiff>270 and TimeDiff<=300,Dual('>270- <=300',10),

if(TimeDiff>300 and TimeDiff<=330,Dual('>300- <=330',11),

if(TimeDiff>330 and TimeDiff<=360,Dual('>330- <=360',12),

if(TimeDiff>360 ,Dual('>360',13)))))))))))))) as TimeDiff1;

LOAD Inter as TimeDiff,

Amount

FROM

[Untitled 2.xls]

(biff, embedded labels, table is [Sheet1$]);

Re: group time intervals into time range

Mon, 19 May 2014 06:01:56 GMT

Must my timediff be in num format? Now it's in text format.

Re: group time intervals into time range

nizamsha — Mon, 19 May 2014 06:13:19 GMT

hi rachel

when using dual function display will be in text format but sorting is done in ascending number format

while using in chart it will show in ascending format

for eg <=30

>30<=60

>60<=90

and so on

or else it will show like these

<=30

>60<=90

>30<=60

and so on

Re: group time intervals into time range

nizamsha — Mon, 19 May 2014 06:14:06 GMT

do u want like this

30-60

60-90

90-120

and so on

Re: Re: group time intervals into time range

nagaiank — Mon, 19 May 2014 08:31:24 GMT

Hope the attached example helps.