2019: Week 28

Recently here at Chin & Beard Suds Co, we've become suspicious that some our employees in our flagship store (found on the 96th floor of the Shard) are slacking off and taking their jobs for granted.  We think their productivity might be linked with how close they are to the nearest manager and who they're interacting with.

To investigate this we've spent time undercover observing 3 random employees during a typical working day and recording whether they're on task. We record in intervals of between 1 & 3 mins and also note down the rough proximity of the manager on duty and who they interacted with most during each interval. We've also noted down the time we started observing each employee but not the actual start time of each interval.

This current data format doesn't work well in Tableau, so we need to do some clean-up and find a way to get some actual date-times for each interval.

The input.

Requirements

• Input the data file.
• For each employee and interval:

1. Create a single field for who they interacted with.
2. Create a single field for the manager's proximity.
3. Create a single field for whether they were on task or not.
4. Calculate the actual start time.

• Output the file.

Output

The output (formatted as a table for clarity).

• 7 fields.
• 90 rows (91 including headers).

The output can be found here for comparison. Don't to forget to fill in our participation tracker and share your solutions with us using #PreppinData on Twitter!

--------------------------- SPOILERS BELOW ---------------------------

Hints

• Imagine you're helping Ross Geller get a couch up some stairs.
• The date can be found in the sheet name and you don't need to manually type it out.
• A running total followed by a date calculation can help get the interval start times.