Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
andriesb
Contributor

Time difference in decimal format.

I have two day time values within a database and want to create a decimal calculated value as a new field within my script to show the total hours in a decimal format.

time1   20120815153000

time2   20120815070000

Format:YYYYMMDDHHMMSS

Within this example my end time is 15h30 and my starting time is 07h30. I would like to have an integer value of 8.

A second attempt would be to also use the amount of day's:

(Left(Dte,8) - Left(Dts,8) + 1) * ((Dte - Dts)/100) AS IZ.TotaalHours,

This works except for the hour part.

1 Solution

Accepted Solutions
andriesb
Contributor

Re: Time difference in decimal format.

I actually managed to solve my problem in a rather easy way with your suggested timestamp:

This could be done within the original scripting without an extra load.

Round(( Timestamp#(time2,'yyyymmddhhmmss') - Timestamp#(time1,'yyyymmddhhmmss')) * 24, 0.1)

This will format my times, will subtract them and convert them into a number: multiplication with 24 will effectly resolve it into a needed decimal number.

As these are quite large numbers, the round function for this example will round the results into the needed 8,5 hours.

A second stage could be to calculate the amount of days. using ony the 8 left characters and multiply this with the hours / day.

3 Replies
MVP
MVP

Re: Time difference in decimal format.

First, inteprete your datetime values correctly using e.g. timestamp#() function.

Then you can calculate your time difference using interval() function.

LOAD *,

     num#(Interval(time1-time2,'h')) as DurationHours;

LOAD timestamp(Timestamp#(time1,'YYYYMMDDhhmmss')) as time1,

     timestamp(Timestamp#(time2,'YYYYMMDDhhmmss')) as time2

INLINE [

time1, time2  

20120815153000,20120815070000

]

andriesb
Contributor

Re: Time difference in decimal format.

I actually managed to solve my problem in a rather easy way with your suggested timestamp:

This could be done within the original scripting without an extra load.

Round(( Timestamp#(time2,'yyyymmddhhmmss') - Timestamp#(time1,'yyyymmddhhmmss')) * 24, 0.1)

This will format my times, will subtract them and convert them into a number: multiplication with 24 will effectly resolve it into a needed decimal number.

As these are quite large numbers, the round function for this example will round the results into the needed 8,5 hours.

A second stage could be to calculate the amount of days. using ony the 8 left characters and multiply this with the hours / day.

MVP
MVP

Re: Time difference in decimal format.

Just reuse the timestamp difference (the integer part is giving the number of days):

Round(( Timestamp#(time2,'yyyymmddhhmmss') - Timestamp#(time1,'yyyymmddhhmmss')) )

to get the number of days rounded or

floor(( Timestamp#(time2,'yyyymmddhhmmss') - Timestamp#(time1,'yyyymmddhhmmss')) )

to get the number of days, only full days counted.

Community Browser