Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
praveenkumar_s
Creator II
Creator II

Need to find difference in minutes using two timestamp fields

Hi friends,

I just need to find the difference between two timestamp columns by minutes and also i have to remove seconds . Any suggestion would be really helpful.

eg: jan 12,2017 11:27:28 PM and jan 12,2017 11:37:22 PM

Actual answer using interval function including seconds: 9 minutes

Expected answer : 10 minutes (remove seconds and have to find difference)

23 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

I know it looks rather ugly, but AFAIK you can only consistently produce this outcome by doing exactly what you tell us to do: manupulate the seconds in the TimeStamp strings.

Create a Straight table with dimensions ORIG_RESTORE_TS and NEW_RESTORE_TS and the following expressions:

Strcat(ORIG): =Date#(Left(ORIG_RESTORE_TS, Index(ORIG_RESTORE_TS, ':', 2)) & '00' & Right(ORIG_RESTORE_TS, 3), 'MMM DD, YYYY hh:mm:ss TT')

Strcat(NEW): =IF (Len(NEW_RESTORE_TS) > 0, Date#(Left(NEW_RESTORE_TS, Index(NEW_RESTORE_TS, ':', 2)) & '00' & Right(NEW_RESTORE_TS, 3), 'MMM DD, YYYY hh:mm:ss TT'))

Diff: =Round(([Strcat(ORIG)] - [Strcat(NEW)])*24.0*60.0)

produces this:

Find Difference in minutes thread296135.jpg

No it doesn't look rather ugly, it looks extremely ugly. But it seems to work.

sunny_talwar

Hey Rob - The reason I thought it would need Floor was because the OP wanted to convert 01:55 sec to 01 min ,  02:25 sec to 02 min. Ceil will convert it to 02 and 03, respectively, right?

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Indeed, and at the other end: Floor(-01:55) = -2 and Floor(02:25) = -3 too.

And you can't switch between Floor() and Ceil() in your expression, depending on whether TimeStamp2 (or ORIG_RESTORE_TS) Is GE or LT than TimeStamp1, because that breaks other rows. Tough one...

sasiparupudi1
Master III
Master III

May be try like

Interval(

(TimeStamp(Timestamp#(ts1,'MMM DD,YYYY hh:mm:ss TT'),'MMM DD,YYYY hh:mm')

TimeStamp(Timestamp#(ts2,'MMM DD,YYYY hh:mm:ss TT'),'MMM DD,YYYY hh:mm'))

,

'mm')

sunny_talwar

That is why I wasn't flooring the subtraction (well initially I did), but flooring the individual time stamps and then perform the subtraction

praveenkumar_s
Creator II
Creator II
Author

hi peter,

I tried the method which you have done. but i didnt get any value in diff column(restore variation in my table) as see in the attachment below,

timeissue.PNG

sunny_talwar

Can you share your qvw file here or data in an Excel file?

Peter_Cammaert
Partner - Champion III
Partner - Champion III

It looks like both the Strcat columns contain strings instead of duals. The expression in the Diff column tries to perform calculations on numerical values and those calculations won't work with strings. What expressions did you use in the Strcat columns?

praveenkumar_s
Creator II
Creator II
Author

I have used the expression you gave. find below,

Strcat(ORIG):=Date#(Left(ORIG_RESTORE_TS, Index(ORIG_RESTORE_TS, ':', 2)) & '00' & Right(ORIG_RESTORE_TS, 3), 'MMM DD, YYYY hh:mm:ss TT')

praveenkumar_s
Creator II
Creator II
Author

sunny,

Please find the QVW and sample source file