group time intervals into time range

[#1]

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.

[nagaiank]

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

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

[Anonymous]

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.

[#4]

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.

[Anonymous]

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

[Anonymous]

Use Class functions as it is used to define intervals:

Class(timediff,30,'x')

[nizamsha]

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

[#8]

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

[nizamsha]

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]

do u want like this

30

30-60

60-90

90-120

and so on