Discussion Board for collaboration on QlikView Scripting.
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.
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:
This works except for the hour part.
Go to Solution.
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.
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;
LOAD timestamp(Timestamp#(time1,'YYYYMMDDhhmmss')) as time1,
timestamp(Timestamp#(time2,'YYYYMMDDhhmmss')) as time2
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.