Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to divide the duration into intervals like 15 mins,1 Hr,24 Hr...

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

10 Replies
Anonymous
Not applicable
Author

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.

MarcoWedel

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

Not applicable
Author

Hi Marco,

small doubt regarding the above script. what is the value of 1/96=? ,1/24=?, 1 means plz explain me

maxgro
MVP
MVP

see attachment

I added some rows to your excel and changed 1 value (year 20124)


RESULT

1.png

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

MarcoWedel

1/24 = 1 day / 24 = 1 hour

1/96 = 1/4 hour = 15 min

Hope this helps

Regards

Marco

Not applicable
Author

Thank you Marco

MarcoWedel

You're welcome.

Please close this thread, if your question is answered.

Thanks

Regards

Marco

Not applicable
Author

How to close the thread

MarcoWedel

Mark an answer of your choice correct (if you like others helpful as well).

Thanks

Regards

Marco