topic Difference Timestamp on Networkdays and between fixed hours of operation in QlikView

Difference Timestamp on Networkdays and between fixed hours of operation

Thu, 14 May 2009 21:02:15 GMT

Hi,

Problemdescription:
I want to calculate the difference between 2 timestamps under consideration that the difference should be only calculated on:
- Networkdays
- between fixed hours of operation

Hours of operation:
Type A: 07:00 - 18:00
Type B: 06:00 - 22:00

Please use the attached .qvw file for suggestions for a solution. I hope somebody could help me. Thanks.

Regards,

Herbert

Difference Timestamp on Networkdays and between fixed hours of operation

disqr_rm — Thu, 14 May 2009 22:08:38 GMT

How many records you would have in the "Table"?

Would it be acceptable to loop on the Table records and performing the calculations?

Difference Timestamp on Networkdays and between fixed hours of operation

Thu, 14 May 2009 22:21:41 GMT

Hi,

There are about 2000 rows in my real application.

I don't know how long this takes for calculating, I am very new to QlikView, but I think if it is possible everything should be done in the skript ==> creating a field where the difference is inside.

But I have no idea how I can solve this. My .qvw file is the the question thread.

Thanks for helping me.

Regards,

Herbert

Difference Timestamp on Networkdays and between fixed hours of operation

Thu, 14 May 2009 23:22:27 GMT

It should be easy to calculate in your script. Try using the if statements, and date time functions.

Logic is simple:

total Time= Time on StartDate + Time on EndDate + NumberofHours*NetWorkingDays in between

Time on StartDate = rangemax(OfficeEndTime - startTime ,0) if StartDate is workingDay otherwise 0

Time on EndDate =rangemax( endTime-OfficeStartTime, 0) If EndDate is workingDay otherwise 0

Difference Timestamp on Networkdays and between fixed hours of operation

disqr_rm — Thu, 14 May 2009 23:24:49 GMT

Here you go. Something quick and dirty. You would get the idea.

Hope this helps.

Difference Timestamp on Networkdays and between fixed hours of operation

Thu, 14 May 2009 23:42:28 GMT

Thanks to both solutions from Verma and Rakesh, I have tried the one from Rakesh, and on the first look it works fine and correct. But I must go through the skript to understand it is a little bit complex.

I will also try the solution from Verma and report here later than.

Thank you very much.

Regards,

Herbert

Difference Timestamp on Networkdays and between fixed hours of operation

Mon, 18 May 2009 22:55:03 GMT

Here is the one I was talking about. An Application without using any loops and still giving you the results that you want. Let me know how you find it.

Using loops for this problem is not the best way.

Best Regards,

Vidyut

Difference Timestamp on Networkdays and between fixed hours of operation

Tue, 19 May 2009 14:30:46 GMT

Thank you for giving me the solution of your idea, I was not able to implement it correctly. I tested it and it works fine, and I think it is better than using loops, because when the difference between 2 dates it to big, that the skript execution takes more time. Thanks for the 2 solutions.

Regards,

Herbert

Difference Timestamp on Networkdays and between fixed hours of operation

Wed, 16 Sep 2009 15:43:35 GMT

Hi,

I need the help of the community for my problem described above. I got 2 solutions the one from Rakesh with loops, but it gets very slow when the data gets bigger and the date difference between the timestamps are also very big.

So I used now the solution von Verma, it is fast but is not working correct in any situation. It works fine if start date and enddate are different dates, but you get false results if the startdate is the same as the enddate, than the solution does not work anymore.

For example 16.04.2009 15:00 until 18.04.2009 20:00 ==> difference is OK,

14.09.2009 09:06 until 14.09.2009 09:16 ==> difference is false,

I attached a sample .qvw for the solution from Verma, maybe somebody could help me to get it working correctly also if the startdate is the same date as the enddate. Or somebody could give me another solution.

Thanks,

Regards,

Herbert