topic Re: Finding the difference between timestamps in QlikView

Finding the difference between timestamps

Tue, 16 Oct 2012 16:03:57 GMT

I have data that corresponds to several groups of field technicians and the time that they start and complete each job during the day. This timestamp information comes across as HH:MM:SS

I am looking for the when a tech starts his FIRST job of the day and when he completes his LAST job of the day. I have been able to find the particular timestamps for this information by using Maxstring and Minstring to display this information.

My question revolves around finding the difference between these two values. IE, a tech starts at 8am and finishes at 1:30pm so therefore he worked 5.5 hours.

Any help would be appreciated.

Re: Finding the difference between timestamps

swuehl — Tue, 16 Oct 2012 16:10:01 GMT

You should let QV interprete your timestamps as such, resulting in values having a numerical part.

To achieve this, use the time interpretion function time#(), like

LOAD

time#(TIMEFIELD, 'hh:mm:ss') as TIMEFIELD,

...

from YourTable;

If QV has interpreted your values correctly, you can then use interval() function to get the difference in time:

=interval(ENDTIMEFIELD-STARTTIMEFIELD)

=interval( max(TIMEFIELD) - min(TIMEFIELD) )

Hope this helps,

Stefan

Re: Finding the difference between timestamps

Tue, 16 Oct 2012 17:24:47 GMT

Thank you, that works perfectly.

I found that i could also do the same thing by changing the data type directly using the number tab in my chart properties.

I really appreciate the quick response.

Re: Finding the difference between timestamps

Tue, 16 Oct 2012 17:25:03 GMT

Thank you, that works perfectly.

I found that i could also do the same thing by changing the data type directly using the number tab in my chart properties.

I really appreciate the quick response.