topic Re: Timesheet Loop in QlikView

Timesheet Loop

Karl_Hart — Thu, 18 Jun 2015 19:59:22 GMT

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

Re: Timesheet Loop

MarcoWedel — Thu, 18 Jun 2015 21:59:45 GMT

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

Re: Timesheet Loop

MarcoWedel — Thu, 18 Jun 2015 22:04:26 GMT

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

Re: Timesheet Loop

Karl_Hart — Sat, 20 Jun 2015 19:57:04 GMT

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

Re: Timesheet Loop

MarcoWedel — Sat, 20 Jun 2015 20:20:10 GMT

You're welcome.

Glad you liked it.

regards

Marco