topic Re: Copare time with different format in QlikView

Copare time with different format

stekol61 — Thu, 24 Jan 2019 14:26:05 GMT

Hi!

I have a app where i want to calculate if 'Response time' is within 'Time_SLA' or not.

The problem I have seems to be that the date format is different. Example below:

Time_SLA =00:04:00 (dd:hh:mm)

Response time = 01:22:21 (hh:mm:ss)

Re: Copare time with different format

Jacek — Thu, 24 Jan 2019 15:37:25 GMT

I would suggest convert all times to minutes and then compare. Below I created a code using subfield, should work correctly but maybe there is an easier way:

= if( 
// Time_SLA  (dd:hh:mm)
rangesum( Num(SubField(Time_SLA,':', 1) ) * 24 * 60, num( SubField(Time_SLA,':', 2)) * 60,  Num(SubField(Time_SLA,':', 3)) ) 
// Response time  (hh:mm:ss)
- rangesum( num( SubField(Response_time,':', 1)) * 60,  Num(SubField(Response_time,':', 2)) ) 

>= 0 , 'in time', 'longer'
)

Re: Copare time with different format

dplr-rn — Thu, 24 Jan 2019 15:40:24 GMT

as this is not a proper date i suggest you covert both those fields into minutes sla 240 mins, response time 82.35 mins
which will make it easier to compare.
so use subfield function to create this
e.g.

Num#(Subfield(TIME_SLA,':',1)) * 24*60 // days
+ Num#(Subfield(TIME_SLA,':',2)) *60  //hrs
+ Num#(Subfield(TIME_SLA,':',3)) // mins
as TIME_SLA_IN_MINS

Re: Copare time with different format

rwunderlich — Thu, 24 Jan 2019 16:48:49 GMT

the Interval#() function will convert these time strings into a common number:

Interval([Response time], 'hh:mm:ss') < Interval#(Time_SLA), 'dd:hh:mm')

Your Response Time field may already be in numeric format. If you are unsure, put it in a listbox and set the number format to 4 decimals.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com