Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am new to QlikView. I am preparing a report where I need to calculate the time between two dates to be converted in hours.
How do I go about writing the script.
Regards
Mahendra
Dates / timestamps in Qlik are stored as dual values, i.e. they have an underlying numeric values (integers denoting days since Dec 30th, 1899, fraction of decimals denoting time of the day, i.e. 0.25 represents 6 AM) and a text representation (showing the date / timestamp in the format you want).
So it's important to ensure your dates / timestamps are read in correctly when you load your data:
Then it's easy to calculate the difference between two timestamps, you can just subtract the values:
LOAD
EndTimestampField - StartTimestampField as Duration
EndTimestampField,
StartTimestampField,
...
FROM ..;
This will show a number, which you then can format as you like using Interval() function:
Interval( EndTimestampField - StartTimestampField, 'hh:mm:ss') as Duration
P.S. The Duration values are like dates showing hours as fractions of days (0.25 represents 6 hours, 1.25 represents 30 hours. If you need your values to represent hours in integer values, you can multiply by 24 and round your values).
Round( ( EndTimestampField - StartTimestampField)*24) as DurationInHours