topic Re: Timestamp Calculation Help in QlikView

Timestamp Calculation Help

abhaysingh — Wed, 14 Oct 2015 11:18:48 GMT

Hi Guys,

I have One scenario in Which i have to subtract two datestamp e.g.

Date1 Date2 Date2-Date1

12/10/15 11:10:00 12/10/15 11:15:00 Output Should be 15 Minutes

12/10/15 11:10:00 13/10/15 10:10:00 Output Should be 1380 Minutes

I m Using (Frac(Date2)-Frac(Date1)) * 1440 for Minutes

and getting Correct output for case 1, but getting -ve output in case 2

Please Suggest..

thanks

Abhay

Re: Timestamp Calculation Help

maxgro — Wed, 14 Oct 2015 11:23:37 GMT

interval(

Date#(Date2, 'DD/MM/YY hh:mm:ss')

- Date#(Date1, 'DD/MM/YY hh:mm:ss')

, 'mm')

Re: Timestamp Calculation Help

Kushal_Chawda — Wed, 14 Oct 2015 11:27:03 GMT

try this

LOAD *,

Interval(Time2-Time1,'m')&' Minutes' as Minutes;

LOAD *, timestamp(Timestamp#(Date1,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as Time1,

timestamp(Timestamp#(Date2,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as Time2 Inline [

Date1 , Date2

12/10/15 11:10:00, 12/10/15 11:15:00

12/10/15 11:10:00, 13/10/15 10:10:00 ];

Re: Timestamp Calculation Help

abhaysingh — Wed, 14 Oct 2015 12:32:34 GMT

Hi Maxgro,

I got i output but i need one more calculation in this. i.e

let say the field name is Resolution time

if( Resolution time >= 0 and Resolution time<= 30 ,' under 30 Min',

if( Resolution time >= 30 and Resolution time<= 60 ,' 1 hour')) and so on

I m trying the same mentioned way but not working..

thanks

Abhay

Re: Timestamp Calculation Help

maxgro — Wed, 14 Oct 2015 13:02:13 GMT

you can add as many if you want

if(Date#(Date2, 'DD/MM/YY hh:mm:ss') - Date#(Date1, 'DD/MM/YY hh:mm:ss') < 30/(24*60), '<30',

if(Date#(Date2, 'DD/MM/YY hh:mm:ss') - Date#(Date1, 'DD/MM/YY hh:mm:ss') < 60/(24*60), '<60',

if(Date#(Date2, 'DD/MM/YY hh:mm:ss') - Date#(Date1, 'DD/MM/YY hh:mm:ss') < 120/(24*60), '<120',

'other'

)))

Re: Timestamp Calculation Help

jonathandienst — Wed, 14 Oct 2015 13:11:11 GMT

You were on the right track, bit keep the date portion as well

(Date2 - Date1) * 1440 for Minutes