Calculating Work & Pause

[#1]

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)

[maxgro]

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; 

[Kushal_Chawda]

Please see the attached file

[#4]

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

[#5]

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

[Kushal_Chawda]

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

[#7]

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

[Kushal_Chawda]

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?

[#9]

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

[ramoncova06 · Accepted solution]

here is another option, same assumptions as Massimo