topic Working Hours in QlikView

Working Hours

Fri, 21 Dec 2012 14:33:24 GMT

Hi,

I have the below data:

empno	name	type	no	fac	time	date	status
123	abc	12	99	6	6:49:24	8/7/2012	Time IN
123	abc	12	99	6	8:51:53	8/7/2012	Time OUT
123	abc	12	99	6	10:30:47	8/7/2012	Time IN
123	abc	12	99	6	15:16:44	8/7/2012	Time OUT
123	abc	12	99	6	23:30:47	8/9/2012	Time IN
123	abc	12	99	6	7:16:44	8/10/2012	Time OUT

On 7th, it is easy for me to calculate the Working Hours with Max(OutTime) - Min (InTime).

But for 9th and 10th, how to calculate the Working Hours.?

It will be 10/8/2012 7:16 AM - 09/08/2012 11:30 PM but I am unable to get the values.

Please help...

Re: Working Hours

Fri, 21 Dec 2012 14:55:53 GMT

Use the Interval() function.

Re: Working Hours

SunilChauhan — Fri, 21 Dec 2012 16:55:38 GMT

t2:

INLINE [

empno, name, type, no, fac, time, date, status

123, abc, 12, 99, 6, 6:49:24, 08-07-2012, Time IN

123, abc, 12, 99, 6, 8:51:53, 08-07-2012, Time OUT

123, abc, 12, 99, 6, 10:30:47, 08-07-2012, Time IN

123, abc, 12, 99, 6, 15:16:44, 08-07-2012, Time OUT

123, abc, 12, 99, 6, 23:30:47, 08-09-2012, Time IN

123, abc, 12, 99, 6, 7:16:44, 08-10-2012, Time OUT

];

T1:

Load *,

if(previous(status)='Time IN' and status='Time OUT' and previous(empno)=empno,interval(date+time -previous(date+time)),'') as nethour

resident t2;

drop table t2;

Please try this hope this helps

Re: Working Hours

Sat, 22 Dec 2012 16:19:56 GMT

Hi,

Thank you but i dont knw where I am going wrong. It is not working.. It is still taking the last out time on that day and In time on that day, it is not taking the next day value. It is also otherwise calculating Working Hours between every In and Out Status.

Eg: 10-8-12.. (Normal Timings)

1. I need to pick the Min In Time -- First In Time.

2. I need to pick the Max In Time - Last Out Time.

3. Working Hrs = Max Hrs - Min Hrs

Eg: 11-8-12 (Night Shift)

1. I need to pick the Min In Time (11-8-12 First In Time).

2. I need to pick the Max In Time (12-8-12 Last Out Time).

3. Working Hrs = Max Hrs - Min Hrs (12-8-12 Last Out Time - 11-8-12 First In Time)

I have another problem, here in case my first in time is 22:00 and have even 00:24 time in my data, then min(time) is taken as 00:24. So calculation goes wrong...

Re: Working Hours

Thu, 27 Dec 2012 05:17:24 GMT

Hi,

Any solution..?

Thanks...

Re: Working Hours

jagan — Thu, 27 Dec 2012 06:22:03 GMT

Hi,

Please find attached file, hope this is what you are expecting.

Regards,

Jagan.

Re: Working Hours

Thu, 27 Dec 2012 06:28:00 GMT

Hi Jagan,

For 9 and 10th Calculation is perfect..

bt on 7th, it has to ideally calculate, 15:16 (last out time) - 6:49 (first in time)... which is 8:27.. But it calculates in between every in time too... and I am sure the same might happen w.r.t night shift too if there are many in and out time.

Thanks...

Re: Working Hours

jagan — Thu, 27 Dec 2012 06:51:12 GMT

Hi Shilpa,

PFA.

Regards,

Jagan.

Re: Working Hours

Thu, 27 Dec 2012 10:08:29 GMT

Hi Jagan,

That works fine and after reloading I realised i've missed a possibility... would this code hold good even for the below data

empno	name	type	no	fac	time	date	status	Shift (only for reference, not part of the data)
123	abc	12	99	6	6:49:24	8/7/2012	Time IN	First
123	abc	12	99	6	8:51:53	8/7/2012	Time OUT
123	abc	12	99	6	10:30:47	8/7/2012	Time IN
123	abc	12	99	6	15:16:44	8/7/2012	Time OUT
123	abc	12	99	6	23:30:47	8/9/2012	Time IN	Third
123	abc	12	99	6	7:16:44	8/10/2012	Time OUT	Third
123	abc	12	99	6	23:15:00	8/10/2012	Time IN	Third
123	abc	12	99	6	0:03:00	8/11/2012	Time OUT
123	abc	12	99	6	2:50:00	8/11/2012	Time IN
123	abc	12	99	6	6:45:00	8/11/2012	Time OUT
123	abc	12	99	6	15:45:00	8/11/2012	Time IN	Second
123	abc	12	99	6	17:00:00	8/11/2012	Time OUT
123	abc	12	99	6	17:02:00	8/11/2012	Time IN
123	abc	12	99	6	21:00:09	8/11/2012	Time OUT
123	abc	12	99	6	22:23:00	8/11/2012	Time IN
123	abc	12	99	6	23:00:00	8/11/2012	Time OUT

Thanks...

Re: Working Hours

jagan — Thu, 27 Dec 2012 11:06:12 GMT

Hi Shilpa,

I think you are referring below values, I think the code which I given doesn't handle this

8/10/2012

Time IN

8/11/2012

Time OUT

There should be some field which holds the working date or some thing which tells you on which working day the entry belongs to. Then only it is possible, with this data it is difficult to handle.

Regards,

Jagan.