topic Re: Calculating Work & Pause in QlikView

Calculating Work & Pause

Sat, 20 Jun 2015 13:15:31 GMT

Hi Qliks,

I am still discovering QV and have a challenge at hand...

1) I want to calculate duty times (availability)

2) I need to count the pauses and add all pause times during the duty (hence: calculating effective working time).

This is an example of my XLS log for one specific duty: (number of pauses will fluctuate based on employee)

date	time	employee	available	device	voip
24-jan-15	23:00:00	1001	1	756555	0
24-jan-15	23:50:09	1001	0	toggle pause	1
24-jan-15	0:11:51	1001	1	toggle pause	1
25-jan-15	0:22:59	1001	0	toggle pause	1
25-jan-15	0:31:52	1001	1	toggle pause	1
25-jan-15	0:58:52	1001	0	toggle pause	1
25-jan-15	1:03:56	1001	1	toggle pause	1
25-jan-15	2:15:53	1001	0	toggle pause	1
25-jan-15	2:31:23	1001	1	toggle pause	1
25-jan-15	3:00:00	1001	0	756555	0

So, employee '1001' started his duty on the 24th at 11pm. (Based upon conditions: 'available' = '1' AND 'device' <> 'toggle pause').

He ended the 25th at 3 am (Based upon conditions: 'available' = '0' AND 'device' <> 'toggle pause')

Resulting in the answer:

--> Duty time = 4:00:00 (hours)

Pause is more tricky: at 23:50:09 the employee becomes unavailable. (Based upon conditions: 'available' = '0' AND 'device' = 'toggle pause') {so, he start his first pause}

At 0:11:51 he becomes available again. (Based upon conditions: 'available' = '1' AND 'device' = 'toggle pause') {so, he start his first pause}(effective pause = 0:21:42).

--> He has 4 pauses in this duty.

--> Total pause time = 0:51:09

--> Availability = 78,7%

I would be more than happy if anyone could help me out on this one.

Thank you so much!

Alex

(Going back to the 'QlikView 11 for Developer' book)

Re: Calculating Work & Pause

maxgro — Sat, 20 Jun 2015 14:22:30 GMT

I changed the bold record; also I assumed the pause is always closed (start pause, stop pause)

The idea is to sum the start/stop datetime (sum duty, sum pause)

tmpsource:

load * inline [

date, time, employee, available, device, voip

24-jan-15, 23:00:00, 1001, 1, 756555, 0

24-jan-15, 23:50:09, 1001, 0, toggle pause, 1

25-jan-15, 0:11:51, 1001, 1, toggle pause, 1

25-jan-15, 0:22:59, 1001, 0, toggle pause, 1

25-jan-15, 0:31:52, 1001, 1, toggle pause, 1

25-jan-15, 0:58:52, 1001, 0, toggle pause, 1

25-jan-15, 1:03:56, 1001, 1, toggle pause, 1

25-jan-15, 2:15:53, 1001, 0, toggle pause, 1

25-jan-15, 2:31:23, 1001, 1, toggle pause, 1

25-jan-15, 3:00:00, 1001, 0, 756555, 0

];

source:

load

employee, available, device, voip,

Timestamp(date#(date, 'DD-MMM-YY') + time#(time, 'hh:mm:ss')) as dt ;

load

date, time, employee, available, device, voip

Resident tmpsource;

DROP Table tmpsource;

calc:

load

employee,

interval(sum(dt * if(available, -1, 1))) as dutytime

Resident source

where not wildmatch(device, 'toggle*')

group by employee;

left join (calc)

load

employee,

interval(sum(dt * if(available, 1, -1))) as pausetime

Resident source

where wildmatch(device, 'toggle*')

group by employee;

Re: Calculating Work & Pause

Kushal_Chawda — Sat, 20 Jun 2015 20:25:37 GMT

Please see the attached file

Re: Calculating Work & Pause

Wed, 24 Jun 2015 17:24:23 GMT

Hello Kush

Thank you so much for your script. I have been sick but finally got better and worked on it this afternoon. Unfortunately it will only work if the inline data is very small.

I have an excel file, which I will enclose. Running your script on that, will not result in any usuable calculations. Perhaps it will be a breeze for you, but the XLSX file made my QV run at 16 GB internal mem and 99% cpu for one hour (it kept running by the way) and wrote 1.2 billion results into the :final table. which then was unreadable 🙂

I believe one if statement should be implemented: if the employee does not log off, the last registered time, should be considered to be his duty end time. I have placed a remark in the XLSX file on row 360 where this did happen.

I would be more than happy to thank you for your time invested. Please contact me via kush141087@aksy.nl

Alex

Re: Calculating Work & Pause

Wed, 24 Jun 2015 17:26:49 GMT

Hi Massimo,

I appreate your time and effort in helping me. Thank you. I have tried your code but it did not really work. I received negative duty hours. I assume the data is not consistent enough which I also explains below to Kush.

I am not sure if you can open his file/code but it worked better (but still need a little tuning which I hope will become available to me and the public. 🙂

Thank you.

Alex

Re: Calculating Work & Pause

Kushal_Chawda — Wed, 24 Jun 2015 18:29:17 GMT

Hi Alexandre,

Have you checked which part of the script is utilizing the more RAM and CPU?

Is your excel file contains the data in same format as in inline table?

You can share your excel file so that I can do some testing

Re: Calculating Work & Pause

Wed, 24 Jun 2015 18:31:56 GMT

Hi Kush, thank you for your quick reaction! I thought I had uploaded the sample XLSX file. Let me try again.

Please email me on the email address provided.

Alex

Re: Calculating Work & Pause

Kushal_Chawda — Wed, 24 Jun 2015 18:52:43 GMT

Thanks for sharing file.

Few doubts

You had shared the example for one device per employee , but here in excel file there are multiple devices per employees,

so do you want to sum Pause, working hrs, pause time for each device or you want to show individual device wise?

Hope my question is clear?

Re: Calculating Work & Pause

Wed, 24 Jun 2015 19:01:03 GMT

Hi Kush,

The device per employee can change. Yes. I will not use the device as a search or 'selectable' in QV. So the only time you need to use the device, is to know when the duty starts.

An employee will use (any) device and the combination of ('Device <> 'Toggle'' AND 'Available = '1'') will mark the duty start time.

('Device <> 'Toggle'' AND 'Available = '0') will mark the end of a duty.

The 'Toggle' value, means that they did not sign off/log off, but started a pause break. Sometimes employees do not know about the pause button and you will see many Log On/Off.

When they use the 'Toggle' to 0 (in pause/unavailable) or '1' for available/ not in pause.

Row 360 shows a employee who did not sign off, but started a 'long' 🙂 pause. That should ring a 'warning' and count the 'in pause' to be 'end of duty'.

Hope it clears.

Alex

Re: Calculating Work & Pause

ramoncova06 — Wed, 24 Jun 2015 19:04:24 GMT

here is another option, same assumptions as Massimo

Re: Calculating Work & Pause

Wed, 24 Jun 2015 19:09:25 GMT

Thanks Ramon,

Will look into this in a minute! Appreciate also your effort. This community is really terrific!

Alex

Re: Calculating Work & Pause

Kushal_Chawda — Wed, 24 Jun 2015 19:14:03 GMT

So In your excel what will be the Duty Start time & Duty End Time for employee 1001?

Re: Calculating Work & Pause

Wed, 24 Jun 2015 19:24:21 GMT

Please find the image where I have worked it out in XLS.

Alex

Re: Calculating Work & Pause

Wed, 24 Jun 2015 21:04:22 GMT

Hi Ramon,

Think you nailed it! I will be double checking if weird things happen in the output when I start expanding the long list of rows in Excel.

I would also like to thank the other contributors just as much! Kush and Massimo: I have learned a lot the last days!

Keep up the good work. It is appreciated by people in (desperate sometimes :-)) need!

Alex

Re: Calculating Work & Pause

ramoncova06 — Wed, 24 Jun 2015 21:23:38 GMT

glad it worked