topic Re: How to calculate the difference between timestamps each one composed by a time, and a date field in QlikView

How to calculate the difference between timestamps each one composed by a time, and a date field

Fri, 01 Nov 2013 19:01:27 GMT

Hi all,

I have an issue to get this done

I have 4 fields:

Datea and Timea

Dateb and Timeb

I need to get the difference between Datea & Timea and Dateb & Timeb.

It would be something like this for example:

Datea = 15/6/2013, Timea = 15:25:00

Dateb = 15/6/2013, Timeb = 15:50:00

The answer should be

Difference = 00/0/0000 00:25:00

Was I clear?

any help will be welcomed.

Nicole-Smith — Fri, 01 Nov 2013 19:07:40 GMT

fabs(subfield(Datea, '/', 1)-subfield(Dateb, '/', 1)) & '/' &

fabs(subfield(Datea, '/', 2)-subfield(Dateb, '/', 2)) & '/' &

fabs(subfield(Datea, '/', 3)-subfield(Dateb, '/', 3)) & ' ' &

interval(fabs(timestamp#(Timea, 'hh:mm:ss')-timestamp#(Timeb, 'hh:mm:ss')), 'hh:mm:ss')

Clever_Anjos — Fri, 01 Nov 2013 19:10:46 GMT

What about

interval(Datea + Timea - (Dateb-Timeb))

Nicole-Smith — Fri, 01 Nov 2013 19:15:51 GMT

I didn't take into account the leading zeroes in my first post:

left('00', 2-len(fabs(subfield(Datea, '/', 1)-subfield(Dateb, '/', 1)))) & fabs(subfield(Datea, '/', 1)-subfield(Dateb, '/', 1)) & '/' &

fabs(subfield(Datea, '/', 2)-subfield(Dateb, '/', 2)) & '/' &

left('0000', 4-len(fabs(subfield(Datea, '/', 3)-subfield(Dateb, '/', 3)))) & fabs(subfield(Datea, '/', 3)-subfield(Dateb, '/', 3)) & ' ' &

interval(fabs(timestamp#(Timea, 'hh:mm:ss')-timestamp#(Timeb, 'hh:mm:ss')), 'hh:mm:ss')

Mon, 04 Nov 2013 11:45:14 GMT

thank you nicole, your second expression solved the problem.