Timesheet Loop

[Karl_Hart]

Hello all,

I have access to our timesheet data and I have the following fields

Name          Time            Event

Joe Blogs     9:30            1

Joe Blogs     12:00          2

Joe Blogs     13:00          1

Joe Blogs     16:00          2

Event 1 is logging in for the day, event 2 is logging out, so Joe Blogs was working between 9:30 and 12 and then between 13:00 and 16:00

I want to create a loop that tells me if this person was at work on the hour each hour. i.e. I want to have the following results:-

Time          At Work?

9:00               N

10:00              Y

11:00               Y

12:00               Y

13:00                N

14:00               Y

15:00               Y

16:00               Y

17:00               N

Would anyone be able to help me create this loop? I'm not sure whether I'll need to do it hourly (as above) or do it for every 30 or 15 minutes so if you can point out how I would change that too, it would be most appreciated

Thanks

[MarcoWedel]

Hi,

one solution that should even work with multiple names and false multiple events could be:

tabWorkTimes:
LOAD Name,
    If(Event=1,Time,If(Name=Previous(Name),Peek(StartTime))) as StartTime,
    If(Event=2,Time) as EndTime
Resident tabTimes
Order By Name, Time;

DROP Table tabTimes;

Right Join (tabWorkTimes)
LOAD Name,
    StartTime,
    Max(EndTime) as EndTime
Resident tabWorkTimes
Where EndTime
Group By Name, StartTime;

tabHours:
LOAD Time(RangeSum(Peek(Time),'01:00')) as Time
AutoGenerate 23;

tabLink:
IntervalMatch (Time)
LOAD StartTime, EndTime
Resident tabWorkTimes;

hope this helps

regards

Marco

[MarcoWedel · Accepted solution]

one solution for 15min intervals:

just change the hours creation like:

tabHours:
LOAD Time(RangeSum(Peek(Time),'00:15')) as Time
AutoGenerate 95;

hope this helps

regards

Marco

[Karl_Hart]

Many thanks Marco, you're a genius.

I did have a slight problem applying it to my date but that turned out to be with the formatting of my time field

[MarcoWedel]

You're welcome.

Glad you liked it.

regards

Marco