topic Re: Calculate hours between two dates in QlikView

Calculate hours between two dates

Anonymous — Fri, 26 Sep 2014 12:43:44 GMT

Hi all,

I want to know the numbers of hours between two dates.

date_creation_h	date_prise_charge_h
02/01/2014 23:14	06/01/2014 09:37
03/01/2014 16:24	06/01/2014 09:35
09/01/2014 03:32	09/01/2014 09:56
11/01/2014 01:05	13/01/2014 10:11
15/01/2014 07:11	15/01/2014 09:44
16/01/2014 16:42	16/01/2014 18:11

In my script :

Main :

SET TimeFormat='hh:mm';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm';

At the loading of my data :

Time(([Date clôture ] - [Date de prise en charge ]),'hh:mm') as tps_traitement

This doesn't work, if somebody have an idea!

Thanks.

Emilien

Re: Calculate hours between two dates

PrashantSangle — Fri, 26 Sep 2014 12:46:24 GMT

Hi,

Use Interval()

like interval(dateField2-dateField1,'hh:mm')

Regards

Re: Calculate hours between two dates

Anonymous — Fri, 26 Sep 2014 12:47:57 GMT

Or to return hours only using 'hh' instead of 'hh:mm'

Re: Calculate hours between two dates

JonnyPoole — Fri, 26 Sep 2014 12:49:36 GMT

i used this script with your data

SET TimeFormat='hh:mm';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm';

Load

Interval( Timestamp(date_creation_h) - Timestamp(date_prise_charge_h)) as Hours,

Timestamp(date_creation_h) as date_creation_h,

Timestamp(date_prise_charge_h) as date_prise_charge_h;

LOAD * INLINE [

date_creation_h, date_prise_charge_h

02/01/2014 23:14, 06/01/2014 09:37

03/01/2014 16:24, 06/01/2014 09:35

09/01/2014 03:32, 09/01/2014 09:56

11/01/2014 01:05, 13/01/2014 10:11

15/01/2014 07:11, 15/01/2014 09:44

16/01/2014 16:42, 16/01/2014 18:11

];

To get this result. Does it work ?

Re: Calculate hours between two dates

MarcoWedel — Fri, 26 Sep 2014 12:50:48 GMT

(date_prise_charge_h-date_creation_h)*24

Re: Calculate hours between two dates

Anonymous — Fri, 26 Sep 2014 13:36:32 GMT

This syntax work also :

date_creation_h, date_prise_charge_h,

interval(date#(date_prise_charge_h, 'DD/MM/YYYY hh:mm') - date#(date_creation_h, 'DD/MM/YYYY hh:mm'), 'hh') as h1,

date#(date_prise_charge_h, 'DD/MM/YYYY hh:mm') - date#(date_creation_h, 'DD/MM/YYYY hh:mm') as h2

Thank you all