Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
You're welcome.
Glad you liked it.
regards
Marco