# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
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.

Tags (5)
1 Solution

Accepted Solutions
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

## 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.

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

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

INLINE [

time1, time2

20120815153000,20120815070000

]

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

## 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.