Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

group time intervals into time range

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.

10 Replies
nagaiank
Valued Contributor III

Re: group time intervals into time range

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

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

kirankkk
Contributor

Re: group time intervals into time range

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.

Not applicable

Re: group time intervals into time range

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.

kirankkk
Contributor

Re: group time intervals into time range

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

gupta_n8
Valued Contributor II

Re: group time intervals into time range

Use Class functions as it is used to define intervals:

Class(timediff,30,'x')

nizamsha
Valued Contributor

Re: group time intervals into time range

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$]);

Not applicable

Re: group time intervals into time range

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

nizamsha
Valued Contributor

Re: group time intervals into time range

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

nizamsha
Valued Contributor

Re: group time intervals into time range

do u want like this

30

30-60

60-90

90-120

and so on

Community Browser