Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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,
...
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.
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,
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,
...
try with
=interval([Resolved Time]-[Open Time],'hh:mm')
Thank you Jhon it works perfeclty!!