Hello All,
I have been trying to do this for a while now but have been unable to do so.
Hopefully one of you would be able to help if you have the time.
I have a table that is used to store timecard information which is gathered from biometric terminals for when employees turn up for work.
The table has the following configuration:
ID (INT & UNIQUE)
RECDATE,
RECTIME,
CANCELLUNCH (BOOL),
EMPLOYEENUM (INT).
Every day an employee will turn up to work and sign in biometrically. This will add a record to the table. At the end of their shift they swipe out, again adding another record. They can then swipe again and cancel the lunch deduction if they did not take a lunch. So at a minimum each employee will have two records per shift with an optional third should they cancel the lunch deduction (shown below in table).
Data in the table would look as follows (uk date format):
ID RECDATE RECTIME CANCELLUNCH EMPLOYEENUM
1 23/07/12 13:00:00 0 200
2 24/07/12 01:00:00 0 200
3 24/07/12 01:00:00 1 200
4 23/07/12 09:00:00 0 201
5 23/07/12 18:30:00 0 201
The ID column does not show the exact order of the scans as this is just an example of such data. Aka in real life ID 4 would be in the system before 3.
With record 3 this would be done physically straight after record 2 but in the database they have the exact sme time (01:00:00) for some reason. Possibly the system relating that employees punch out to the cancel lunch record via employee-date composite?
Now ID 1, 2 and 3 relate in business terms yet in the table other than by EMPLOYEENUM they do not. They are one shift for employee 200. I can see this from the table by looking but qlikview cannot see this as the records do not relate via the data (difficult to explain what I mean but hope I have done so).
What I am looking to have is a new table which takes each related series of records down to one as seen with the below table:
ID DATEIN TIMEIN DATEOUT TIMEOUT CANCELLUNCH EMPLOYEENUM
1 23/07/12 13:00:00 24/07/12 01:00:00 1 200
2 23/07/12 09:00:00 23/07/12 18:30:00 0 201
As seen this has now made two related records based on the data available.
The IDs may not follow in order for each employees visit clocking out at we have 12+devices feeding the information to the database.
Any idea on how this can be done would be very helpful as this is proving to be quite difficult for me to accomplish.
The reason why I am trying to get these onto one record is that they can then relate. I can add another field called ShiftHours that will take for that shift the number of hours between the time in and time out. This is not possible from what I understand with the data in seperate records as they are now (as they do not relate).
Thanks in advance for any help...
Regards,
Jim
