topic Re: Difference in hours between two timestamps in QlikView

Difference in hours between two timestamps

Mon, 15 Aug 2011 11:57:07 GMT

Hi Everyone,

I have a fixed timestamp 11/08/2011 20:00 and then I have data collected over a time period with timestamps attached

e.g.

10/08/2011 16:32 , A

10/08/2011 16:45, A

11/08/2011 12:33, A

and so on...

I want to create a field that has the number of hours before or after my fixed timestamp. So for example, the following:

Timestamp, hours

11/08/2011 19:23, -1 //because it is within the previous hour

11/08/2011 14:02, -6

11/08/2011 20:00, 0

11/08/2011 20:55, 0 // because it is still within the hour

11/08/2011 21:05, 1

And so on...

Any thanks would be greatly appreciated!

Thanks

Gareth

Re: Difference in hours between two timestamps

lironbaram — Mon, 15 Aug 2011 12:34:54 GMT

hei

attach is an example

hope its helps you

Difference in hours between two timestamps

Mon, 15 Aug 2011 12:53:33 GMT

this would be my solution:

let vTimestamp =MakeDate(2011,8,15)+Time('20:00','hh:mm');
let vTime=now();

let vDifference=ceil(Interval(Interval(vTimestamp,'h') - Interval(vTime,'h'),'h')*24);

TRACE Timestamp: $(vTimestamp) - actual Time: $(vTime) = $(vDifference);

MFG

Re: Difference in hours between two timestamps

swuehl — Mon, 15 Aug 2011 12:53:59 GMT

You might want to look into interval() function, I think that's what you need.

=interval(vFixedTime - TimeStamp, 'hh')

edited by swuehl