topic Re: Difference between two timestamp in QlikView

Difference between two timestamp

Tue, 21 Aug 2012 10:36:03 GMT

Hi,

I am facing a problem in QV11 to figure out the difference between two timestamps, for e.g. (column1) 04-08-2012 05:12:11 AM - (column2) 04-08-2012 05:11:11 PM.

Kindly help me out. Thank you in advance.

Regards

Ram

Re: Difference between two timestamp

swuehl — Tue, 21 Aug 2012 10:49:59 GMT

Try something like this in your script

SET TimestampFormat='DD-MM-YYYY hh:mm:ss TT';

LOAD

field1,

field2,

interval(field1-field2) as TimeStampDiff

FROM YourTable;

Or in a chart expression

=interval(field1-field2)

Re: Difference between two timestamp

sreenivas — Tue, 21 Aug 2012 10:58:42 GMT

=Date('04-08-2012 05:12:11','DD-MM-YYYY hh:mm:ss') -Date('04-08-2012 05:11:11','DD-MM-YYYY hh:mm:ss')

Re: Difference between two timestamp

jagan — Tue, 21 Aug 2012 11:01:02 GMT

Hi,

Try this

=Interval(Num(Date#('04-08-2012 05:11:11 PM', 'DD-MM-YYYY hh:mm:ss tt')) - Num(Date#

('04-08-2012 05:12:11 AM', 'DD-MM-YYYY hh:mm:ss tt')))

will give you result as 11:59:00

Hope this helps you.

Regards,

Jagan.

Re: Difference between two timestamp

jagan — Tue, 21 Aug 2012 11:01:12 GMT

Hi,

Try this

=Interval(Num(Date#('04-08-2012 05:11:11 PM', 'DD-MM-YYYY hh:mm:ss tt')) - Num(Date#

('04-08-2012 05:12:11 AM', 'DD-MM-YYYY hh:mm:ss tt')))

will give you result as 11:59:00

Hope this helps you.

Regards,

Jagan.

Re: Difference between two timestamp

Tue, 21 Aug 2012 11:01:40 GMT

Thank you sparing your time for this. I think AM PM thing is not used in your answer, even hourly difference need to be tracked.

Re: Difference between two timestamp

Tue, 21 Aug 2012 11:25:55 GMT

Nice it works if its hardcoded..can we do the same dynamically...?

Thank you very much for your solution and time

Re: Difference between two timestamp

sreenivas — Tue, 21 Aug 2012 11:27:19 GMT

Yes instead of time add the fieldname

Re: Difference between two timestamp

Tue, 21 Aug 2012 11:43:34 GMT

with column name its not giving me correct output

Re: Difference between two timestamp

sreenivas — Tue, 21 Aug 2012 11:47:29 GMT

Can you send the date format of the field

Re: Difference between two timestamp

Tue, 21 Aug 2012 11:51:14 GMT

'DD-MM-YYYY hh:mm:ss tt'

Re: Difference between two timestamp

sreenivas — Tue, 21 Aug 2012 11:57:32 GMT

=Date(Date#('Fieldname1','DD-MM-YYYY hh:mm:ss tt'),'DD-MM-YYYY hh:mm:ss tt')-

Date(Date#('Fieldname2','DD-MM-YYYY hh:mm:ss tt'),'DD-MM-YYYY hh:mm:ss tt')

Re: Difference between two timestamp

swuehl — Tue, 21 Aug 2012 12:01:53 GMT

Would be helpful if you could tell us what you get and what you expect when using which expression / script snippet.

I am mostly repeating my above post (just adding your two sample values to two sample fields), but this still seems to return a reasonable result:

SET TimestampFormat='DD-MM-YYYY hh:mm:ss TT';

LOAD

column1,

column2,

interval(column1-column2) as TimeStampDiff

INLINE [

column1, column2

04-08-2012 05:12:11 AM,04-08-2012 05:11:11 PM

];

Re: Difference between two timestamp

Thu, 01 Aug 2013 10:59:00 GMT

Guys,

Im facing the same problem. Is it possible to remove the AM and take 16:00:00 instead on 4:00:00 PM

Thanks.

Mpho