New to QlikView

Discussion board where members can get started with QlikView.

New Contributor III

Time between tow timestamps

Hello community,

I have to calculate the time of taking charge of a ticket taking into account only the hours of opening and closing every day.

Example :

open-close

Monday : 08h00 - 12h00 and 14h00 - 16h00

Tuesday : 08h00 - 11h00 and 16h00 - 20h00

Wednesday : 11h00 - 16h00 and 21h00-Thursday08h00

Thursday : 10h00-15h00 and 20h00-Friday09h00

Saturday : 08h00-12h00

Here are the data

Date_recept                Date_traitement

06/02/2017 07:00:00        06/02/2017 15:00:00

06/02/2017 21:00:00        07/02/2017 08:30:00

08/02/2017 22:00:00        09/02/2017 07:00:00

09/02/2017 16:00:00        09/02/2017 20:30:00

11/02/2017 11:00:00        13/02/2017 09:00:00

And I would like to have the following results

Date_recept                     Date_traitement                           Duration

06/02/2017 07:00:00        06/02/2017 15:00:00                    05:00:00

06/02/2017 21:00:00        07/02/2017 08:30:00                    00:30:00

08/02/2017 22:00:00        09/02/2017 07:00:00                    09:00:00

09/02/2017 16:00:00        09/02/2017 20:30:00                    00:30:00

11/02/2017 11:00:00        13/02/2017 09:00:00                    02:00:00

Any ideas ?

15 Replies
MVP

Re: Time between tow timestamps

Go through below link. You will get the idea what to do with your problem.

Honored Contributor

LMGTFY

Contributor III

Re: Time between tow timestamps

try this

Date#(rDate_recept,'MM/DD/YYYY HH:MM:SS') -Date#(Date_traitement,'MM/DD/YYYY HH:MM:SS')

Not applicable

Re: Time between tow timestamps

Hi

Directory;

Start_Time,

End_Time,

Interval(Start_Time-End_Time,'h-mm-ss') as Difference

FROM TABLE

New Contributor III

Re: Time between tow timestamps

Deepak

Thanks, but we should not count the hours outside of the opening and closing hours. As I did in the above examples.

The duration is calculated according to the hours of opening and closing. Hours outside the opening and closing times are ignored

New Contributor III

Re: Time between tow timestamps

Kiran

Thank, but we should not count the hours outside of the opening and closing hours. As I did in the above examples

MVP

Re: Time between tow timestamps

New Contributor III

Re: Time between tow timestamps

@Manish

I tried but it did not meet my expectations.

The opening and closing hours are not fixed, variable for each day.

And the duration is calculated according to the hours of opening and closing. Hours outside the opening and closing times are ignored

MVP

Re: Time between tow timestamps

You have to create two new fields having closing and opening hours and left join them with the fact table.