Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

sreeni_qvd
New Contributor III

Need to find out how many hours working each employee

Hi All,

PFA of Data, Here EMP_ENT_LEA Filed having values zero and One, Zero means enter and One means Leaving or Exist.

We need to find out the

1) Each employee how many hours  worked per day,weekly and monthly

2) Like 8:00 AM to 9:00 AM, 9:00 Am to 10:00 AM  how many employees enter to office

3) Per day how many employees worked more than 8 hours and those employee details

Regards:

Sreeni

Labels (2)
2 Replies

Re: Need to find out how many hours working each employee

Data:

Load

  EMP_ID as EmployeeID,

  EMP_ENT_LEA,

  TimeStamp#(MNT_TS,'DD/MM/YYYY hh:mm') as MNT_TS,

  Date(Floor(TimeStamp#(MNT_TS,'DD/MM/YYYY hh:mm'))) as Date

Inline

[

  EMP_ID, EMP_ENT_LEA, MNT_TS

  1407154358566, 0, 12/06/2014 09:00

  1407154358566, 1, 12/06/2014 11:00

  1407154358566, 0, 12/06/2014 11:16

  1407154358566, 1, 05/06/2014 13:00

  1407154358566, 0, 05/06/2014 14:00

  1407154358566, 1, 05/06/2014 16:00

  1407154358566, 0, 05/06/2014 16:30

  1407154358566, 1, 05/06/2014 18:00

  1407154358577, 0, 12/06/2014 10:00

  1407154358577, 1, 12/06/2014 11:00

  1407154358577, 0, 12/06/2014 11:16

  1407154358577, 1, 05/06/2014 13:00

  1407154358577, 0, 05/06/2014 14:00

  1407154358577, 1, 05/06/2014 16:00

  1407154358577, 0, 05/06/2014 16:30

  1407154358577, 1, 05/06/2014 18:00

];

Temp:

Load

  *,

  IF(EmployeeID = Previous(EmployeeID) and  EMP_ENT_LEA = 0 and Date = Previous(Date), Interval(MNT_TS-Previous(MNT_TS),'hh:mm:ss')) as Break 

Resident Data

Order By EmployeeID, MNT_TS;

Drop Table Data;

Final:

Load

  EmployeeID,

  Date,

  Week(Date) as Week,

  Month(Date) as Month,

  Year(Date) as Year,

  Interval(Max(MNT_TS)-Min(MNT_TS)-SUM(Break),'hh:mm')   as TotalWorkingTime

Resident Temp

Group By EmployeeID, Date;

Drop Table Temp;

Re: Need to find out how many hours working each employee

Just a slight change in the data because the data wasn't making sense

[

  EMP_ID, EMP_ENT_LEA, MNT_TS

  1407154358566, 0, 12/06/2014 09:00

  1407154358566, 1, 12/06/2014 11:00

  1407154358566, 0, 12/06/2014 11:16

  1407154358566, 1, 12/06/2014 13:00 //Changed from 05/06/2014 13:00

  1407154358566, 0, 05/06/2014 14:00

  1407154358566, 1, 05/06/2014 16:00

  1407154358566, 0, 05/06/2014 16:30

  1407154358566, 1, 05/06/2014 18:00

  1407154358577, 0, 12/06/2014 10:00

  1407154358577, 1, 12/06/2014 11:00

  1407154358577, 0, 12/06/2014 11:16

  1407154358577, 1, 12/06/2014 13:00 //Changed from 05/06/2014 13:00

  1407154358577, 0, 05/06/2014 14:00

  1407154358577, 1, 05/06/2014 16:00

  1407154358577, 0, 05/06/2014 16:30

  1407154358577, 1, 05/06/2014 18:00

];

I did not make sense that a employee who entered never left . So I made them exist

Community Browser