topic Re: Hours calc in QlikView

Hours calc

Wed, 08 Feb 2012 09:47:30 GMT

Hi all,

Please assist a newby.

This was probably asked a few times. I serached, but nothing.

I need to calc hours worked per employee per day. Data is from SQL and very simple.

IndivNdx	In/Out	EventTime
3996	IN	02/02/2012 07:52:05 AM
3996	OUT	02/02/2012 04:55:50 PM
6632	IN	02/02/2012 08:04:26 AM
6632	OUT	02/02/2012 12:14:10 PM
6632	IN	02/02/2012 02:41:13 PM
6632	OUT	02/02/2012 05:08:36 PM

Re: Hours calc

Wed, 08 Feb 2012 09:51:08 GMT

Sample table attached

Re: Hours calc

eliran — Wed, 08 Feb 2012 10:00:51 GMT

See the attachment, this is one approach..

Hope it helps,

Eliran.

Re: Hours calc

Sokkorn — Wed, 08 Feb 2012 10:21:59 GMT

Hi,

Use scritp like this

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
[Data]:
LOAD * INLINE [
IndivNdx,     In-Out,    EventTime
3996,    IN,    02-02-2012 07:52:05 AM
3996,    OUT,    02-02-2012 04:55:50 PM
6632,    IN,    02-02-2012 08:04:26 AM
6632,    OUT,    02-02-2012 12:14:10 PM
6632,    IN,    02-02-2012 02:41:13 PM
6632,    OUT,    02-02-2012 05:08:36 PM];
[Data1]:
LOAD
    IndivNdx,
    MIN(EventTime)    AS IN
RESIDENT [Data] WHERE [In-Out]='IN' GROUP BY IndivNdx;
INNER JOIN ([Data1])
[Data2]:
LOAD
    IndivNdx,
    MAX(EventTime)    AS OUT
RESIDENT [Data] WHERE [In-Out]='OUT' GROUP BY IndivNdx;

Next create one Straight Table with IndivNdx as dimension and INTERVAL(OUT-IN,'hh:mm:ss') as expression.

See the sample attached file also.

Regards,

Sokkorn Cheav

Re: Hours calc

Wed, 08 Feb 2012 13:35:33 GMT

Brilliant.

Thanks alot.