topic Re: Time Spent in/outside Room in QlikView

Time Spent in/outside Room

dmohanty — Sat, 08 Jun 2013 11:16:44 GMT

Hi All,

Need a quick help in calculating the time spent by a person (for a month) outside the room at each Swipe OUT, Total Time spent outside room (summation of each time outside), and the Total Time Spent in Office (First Swipe IN time - Last Swipe OUT).

Here is my sample data:

Room	Card No	Swipe Date	SwipeTime	Swipe Status
Room A	12345	2013-04-01	12:32:57	Time IN
Room A	12345	2013-04-01	13:34:50	Time OUT
Room A	12345	2013-04-01	14:59:13	Time IN
Room A	12345	2013-04-01	14:59:48	Time OUT
Room A	12345	2013-04-01	15:00:44	Time IN
Room A	12345	2013-04-01	16:33:21	Time OUT
Room A	12345	2013-04-01	18:06:09	Time IN
Room A	12345	2013-04-01	18:11:24	Time OUT
Room A	12345	2013-04-02	12:28:04	Time IN
Room A	12345	2013-04-02	14:07:08	Time OUT
Room A	12345	2013-04-02	15:40:06	Time IN
Room A	12345	2013-04-02	16:30:19	Time OUT

So I need an extra column "Time Outside Room" to be calculated as if Swipe Status = 'Time IN' , SwipeTime - Previous(SwipeTime). So, the new records would look like this:

Room	Card No	Swipe Date	SwipeTime	Swipe Status	Time Outside Room	Total Hours
Room A	12345	2013-04-01	12:32:57	Time IN
Room A	12345	2013-04-01	13:34:50	Time OUT
Room A	12345	2013-04-01	14:59:13	Time IN	1:24	04:28:00
Room A	12345	2013-04-01	14:59:48	Time OUT
Room A	12345	2013-04-01	15:00:44	Time IN	0:0
Room A	12345	2013-04-01	16:33:21	Time OUT
Room A	12345	2013-04-01	18:06:09	Time IN	1:32
Room A	12345	2013-04-01	18:11:24	Time OUT
Room A	12345	2013-04-02	12:28:04	Time IN
Room A	12345	2013-04-02	14:07:08	Time OUT
Room A	12345	2013-04-02	15:40:06	Time IN	1:32
Room A	12345	2013-04-02	16:30:19	Time OUT

Please help in calculating the values of the Last two fields (Time Outside Room, Total Hours) in above Table.

Appreciate your help!!!

Re: Time Spent in/outside Room

swuehl — Sat, 08 Jun 2013 13:03:15 GMT

You probably need to define the granularity you want to look at when calculating the TOTAL values.

I assumed something like the combination of Room, card number and Swipe Date.

You could then try something like

INPUT:

LOAD *, AutoNumber([Card No]&Room&[Swipe Date]) as Gran

INLINE [

Room, Card No, Swipe Date, SwipeTime, Swipe Status

Room A, 12345, 2013-04-01, 12:32:57, Time IN

Room A, 12345, 2013-04-01, 13:34:50, Time OUT

Room A, 12345, 2013-04-01, 14:59:13, Time IN

Room A, 12345, 2013-04-01, 14:59:48, Time OUT

Room A, 12345, 2013-04-01, 15:00:44, Time IN

Room A, 12345, 2013-04-01, 16:33:21, Time OUT

Room A, 12345, 2013-04-01, 18:06:09, Time IN

Room A, 12345, 2013-04-01, 18:11:24, Time OUT

Room A, 12345, 2013-04-02, 12:28:04, Time IN

Room A, 12345, 2013-04-02, 14:07:08, Time OUT

Room A, 12345, 2013-04-02, 15:40:06, Time IN

Room A, 12345, 2013-04-02, 16:30:19, Time OUT

];

RESULT1:

LOAD *,

if(Gran = Peek(Gran) and [Swipe Status] = 'Time IN',

interval(SwipeTime-peek(SwipeTime))) as TimeOut

Resident INPUT

order by [Card No], Room, [Swipe Date], SwipeTime asc;

DROP TABLE INPUT;

RESULT2:

LOAD Gran,

interval(sum(TimeOut)) as TotalTimeOut,

interval(

max(if([Swipe Status] = 'Time OUT', SwipeTime))

- min(if([Swipe Status] = 'Time IN', SwipeTime))

) as TotalTimeInOffice

Resident RESULT1 group by Gran;

Re: Time Spent in/outside Room

dmohanty — Sun, 09 Jun 2013 17:59:23 GMT

Thanks a lot Swuehl for the quick turnaround.

Yes, you are right that a Key needed to be created. I should have told you to note the Swipe Date as important. Thanks anyways for figuring it out!!!

I may seek some more help later from you to calculate the Efforts of the people coming in Shifts. Ex: If a person Swipes IN at 10:00 pm (Monday) and Swipes OUT at 6:00 am next day (Tuesday) or else with some more conditions. I will post sample data later.

Thanks and have a great day!!!