Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
You may use the Class() function, e.g.
(timediff,30,'x') as [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.
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.
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
Use Class functions as it is used to define intervals:
Class(timediff,30,'x')
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$]);
Must my timediff be in num format? Now it's in text format.
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
do u want like this
30
30-60
60-90
90-120
and so on