Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am calculating the duration in minutes. means close date and enter date the duration is interval (close date- enter date, 'mm'). using this expression to calculate the duration in minutes. divide the close date and enter date into buckets like
15 minutes,
1 Hour,
24 Hour
> 24 Hours
temp:
LOAD serviceid,
[date-entered],
date_closed
if(Interval(date_closed-[date-entered],'mm')<= tmie#(15,'mm'),'<= 15minutes',
if(Interval(date_closed-[date-entered],'mm')> tmie#(15,'mm') and Interval(date_closed-[date-entered],'mm')<=tmie#(60,'mm') ,'<= 1 Hr',
if(Interval(date_closed-[date-entered],'mm')> tmie#(60,'mm') and Interval(date_closed-[date-entered],'mm')<=tmie#(1440,'mm') ,'<= 24 Hr',
if(Interval(date_closed-[date-entered],'mm')> tmie#(1440,'mm') , '> 24 Hr',)))) as category,
interval(date_closed - [date-entered]) as duration
FROM
(ooxml, embedded labels, table is Sheet3);
<= 15 minutes |
<= 1Hr |
> 24 Hrs |
- |
in that expression the Interval(date_closed-[date-entered],'mm') is divide into category.the data fetching only '<= 15 minutes', '<= 1 Hr', '> 24 Hr' but not fetch the data ' <= 24 Hr' .the duration is divided into category is correct or not otherwise any one plz tell me right way for that solution
Your main problem is with the format of your [date entered] and [date closed] fields in excel. QV does not recognise these as dates therefore the QV interval function will not work.
Your data is in an inconsistent format, you need to clean it up first. If it is in excel (and this is not just a sample), then use find / replace to remove all the am and pm character. Use excel's value function to convert to a number and read teh result into QV. This will work for all the rows other than row 4 where the year is nonsense ie 20124.
Once the data has been cleaned, follow your other post on how to create a bucket.
temp:
LOAD *,
If([date_closed]-[date-entered]<= 1/96,'<= 15minutes',
If([date_closed]-[date-entered]> 1/96 and [date_closed]-[date-entered]<=1/24,'<= 1 Hr',
If([date_closed]-[date-entered]> 1/24 and [date_closed]-[date-entered]<= 1,'<= 24 Hr',
If([date_closed]-[date-entered]> 1,'> 24 Hr',)))) as category,
Interval([date_closed] - [date-entered]) as duration;
LOAD serviceid,
Alt(Timestamp#([date-entered], 'DD/MM/YYYY hh:mm[:ssTT]'), Date#([date-entered])) as [date-entered],
Alt(Timestamp#([date_closed], 'DD/MM/YYYY hh:mm[:ss TT]'), Date#([date_closed])) as [date_closed]
FROM [http://community.qlik.com/servlet/JiveServlet/download/620572-129168/service.xlsx] (ooxml, embedded labels, table is Sheet3);
Hi Marco,
small doubt regarding the above script. what is the value of 1/96=? ,1/24=?, 1 means plz explain me
see attachment
I added some rows to your excel and changed 1 value (year 20124)
RESULT
SCRIPT
directory;
temp:
// 3 - calc bucket
load
*,
if(diffmm < 0, '< 0 ',
if(diffmm < 15, '< 15 mm',
if(diffmm < (60), '< 01 hh',
if(diffmm < (24*60), '< 24 hh',
'> 24 hh'
)))) as bucket
;
// 2 - difference in minutes
LOAD
*,
dc -de as diff,
(dc-de)*24*60 as diffmm
;
// 1 - from excel to timestamp with alt function
LOAD serviceid,
[date-entered],
date_closed,
Timestamp(
alt(
date#([date-entered], 'DD/MM/YYYY hh:mm'),
date#([date-entered], 'DD/MM/YYYY'),
date#([date-entered], 'DD/MM/YYYY hh:mm:ssTT'),
), 'DD/MM/YYYY hh:mm:ss TT') as de,
Timestamp(
alt(
date#([date_closed], 'DD/MM/YYYY hh:mm'),
date#([date_closed], 'DD/MM/YYYY'),
date#([date_closed], 'DD/MM/YYYY hh:mm:ss TT'),
date#([date_closed], 'DD/MM/YYYY hh:mm:ssTT'),
), 'DD/MM/YYYY hh:mm:ss TT') as dc
// if(Interval(date_closed-[date-entered],'mm')<= tmie#(15,'mm'),'<= 15minutes',
// if(Interval(date_closed-[date-entered],'mm')> tmie#(15,'mm') and Interval(date_closed-[date-entered],'mm')<=tmie#(60,'mm') ,'<= 1 Hr',
// if(Interval(date_closed-[date-entered],'mm')> tmie#(60,'mm') and Interval(date_closed-[date-entered],'mm')<=tmie#(1440,'mm') ,'<= 24 Hr',
// if(Interval(date_closed-[date-entered],'mm')> tmie#(1440,'mm') , '> 24 Hr',)))) as category,
// interval(date_closed - [date-entered]) as duration
FROM
[service1.xlsx]
(ooxml, embedded labels, table is Sheet3);
1/24 = 1 day / 24 = 1 hour
1/96 = 1/4 hour = 15 min
Hope this helps
Regards
Marco
Thank you Marco
You're welcome.
Please close this thread, if your question is answered.
Thanks
Regards
Marco
How to close the thread
Mark an answer of your choice correct (if you like others helpful as well).
Thanks
Regards
Marco