Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

emilienp
New Contributor III

Calculate hours between two dates

Hi all,

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

date_creation_hdate_prise_charge_h
02/01/2014 23:1406/01/2014 09:37
03/01/2014 16:2406/01/2014 09:35
09/01/2014 03:3209/01/2014 09:56
11/01/2014 01:0513/01/2014 10:11
15/01/2014 07:1115/01/2014 09:44
16/01/2014 16:4216/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

1 Solution

Accepted Solutions
Employee
Employee

Re: Calculate hours between two dates

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 ?

Capture.PNG.png

5 Replies

Re: Calculate hours between two dates

Hi,

Use Interval()

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.

Re: Calculate hours between two dates

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

Employee
Employee

Re: Calculate hours between two dates

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 ?

Capture.PNG.png

Re: Calculate hours between two dates

(date_prise_charge_h-date_creation_h)*24

emilienp
New Contributor III

Re: Calculate hours between two dates

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