Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Substract of Date-Time Fields

Hi guys, how could I do the substract this fields (Resolved Time  -   Open Time)  and get Hours?

Resolved Time                                         Open Time

14/01/2010  10:28:45 a.m.                      04/01/2010  09:35:13 a.m.

thank you all guys!!

1 Solution

Accepted Solutions
MVP
MVP

Re: Substract of Date-Time Fields

Well, if you're doing it at script time, you use the input format in the timestamp#() function, and the desired format in the timestamp() function, or as I generally prefer, in the TimestampFormat variable that you should see getting set at the top of your script.  Hmmm, looks like the timestamp#() function won't like your 'a.m.' either.  So maybe something like this:

SET TimestampFormat='DD/MM/YY hh:mm:ss';

LOAD *
,(RES_TIME - OP_TIME)*24 as "Hours to Resolve"
;
LOAD
timestamp(timestamp#(purgechar([Open Time],'.'))) as OP_TIME,
timestamp(timestamp#(purgechar([Resolved Time],'.'))) as RES_TIME,
...

5 Replies
MVP
MVP

Substract of Date-Time Fields

I assume these are QlikView timestamp fields already?  If so, subtract them and multiply by 24.  If not, use timestamp#() to convert them to timestamps, then subtract them and multiply by 24.

Not applicable

Substract of Date-Time Fields

Thanks Jhon, but is this fortmat that I should convert them to?

Timestamp#([Open Time],'hh:mm:ss') as OP_TIME,

Timestamp#([Resolved Time],'hh:mm:ss') as RES_TIME,

MVP
MVP

Re: Substract of Date-Time Fields

Well, if you're doing it at script time, you use the input format in the timestamp#() function, and the desired format in the timestamp() function, or as I generally prefer, in the TimestampFormat variable that you should see getting set at the top of your script.  Hmmm, looks like the timestamp#() function won't like your 'a.m.' either.  So maybe something like this:

SET TimestampFormat='DD/MM/YY hh:mm:ss';

LOAD *
,(RES_TIME - OP_TIME)*24 as "Hours to Resolve"
;
LOAD
timestamp(timestamp#(purgechar([Open Time],'.'))) as OP_TIME,
timestamp(timestamp#(purgechar([Resolved Time],'.'))) as RES_TIME,
...

Not applicable

Substract of Date-Time Fields

try with

=interval([Resolved Time]-[Open Time],'hh:mm')

Not applicable

Substract of Date-Time Fields

Thank you Jhon it works perfeclty!!

Community Browser