Transforming Biometric data.

[Anonymous]

S.No	Emp ID	Emp Name	Time
1	1	ABC(First-Swipe)	23-01-2019 11:30
2	1	ABC	23-01-2019 12:30
3	1	ABC	23-01-2019 13:30
4	1	ABC(Last-Swipe)	23-01-2019 20:30
5	2	XYZ(First-Swipe)	23-01-2019 11:30
6	2	XYZ	23-01-2019 14:30
7	2	XYZ	23-01-2019 15:30
8	2	XYZ(Last-Swipe)	23-01-2019 21:30

Above are the Biometric entries of 2 company employees for 23rd January each time they enter or exit their workspace.

Based on this I want to calculate 
1. Total hours the employee spent in office.[For ABC: Difference in time of (S.No4-S.No1); For XYZ: Difference in time of(S.No8-S.No5)

Date	Emp Id	Emp Name	In-Time(First swipe)	Out-Time(Last Swipe)
23-01-2019	1	ABC	11:30:00	20:30:00
23-01-2019	2	XYZ	11:30:00	21:30:00

Now, this was just a sample. I have similar data for 70 odd employees for each day. and I aim at getting a daily report. For that I need the data to be transformed in the following manner:

Incase of any queries please comment below.
How can I achieve it in Talend?

[Anonymous]

Hi,

    Even though it looks straightforward in simple use cases, it is having many sub user stories.

    I believe we need to have a type code for IN and OUT swipes else we may classify the IN and OUT swipes wrongly. 

Some example scenarios are:-

a) How we will capture if a person has multiple entries for same day say 10 times IN and OUT swipes. If we are capturing only time, it may error out.

b) Do you need to handle night shifts? If yes, what are the user stories around this?

c) In practical scenarios, employees may miss a swipe like tail gating, swipe was not captured by sensor etc. If we are not having IN/OUT swipe categories and if we are calculating everything only based on time, the entire calculation will become different category and calculation will be totally wrong.

    I am just highlighting some major user stories but there could be multiple more based on your specific use case. So please add these conditions before trying to apply the solution.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Anonymous]

Hi Nikhil,

Thanks for your reply.

You have rightly pointed out the potential scenarios, I'll be addressing them one by one.

a) Presently, the data for multiple swipes of an employee is being stored as separate rows in the database with the timestamp.

b) No, the last shift ends at 10 PM. So no night shifts are to be considered.

c) Tailgating is a major concern, for now If the first and the last swipe can be identified for a particular day, then the 'Total hours' can be calculated.

So to summarize it, for the initial release if the first and last swipes of an employee can be captured for a particular day then the Total Hours can be obtained. So any help in this regard will be helpful.

P.S I have edit the original question for according to the changed requirements

[Anonymous · Accepted solution]

Hi Karan,

      Below logic will help you to find the first and last swipes. Please refer the screenshots below for details.

Sample input data is as below.

The output is as shown below.

The screen shot for each components is as shown below.

The date parsing logic in tMap is as below.

Date ->    TalendDate.parseDate("dd-MM-yyyy",row1.DateTime.substring(0,10)) 
DateTime ->   TalendDate.parseDate("dd-MM-yyyy HH:mm",row1.DateTime) 

The logic to find effective time is tricky as I had described above. The main headache will be tailgating. You will have to arrive at the business logic before doing it.

I hope I have helped to resolve your query for stage 1. Please spend a second to mark the topic as resolved and Kudos are also welcome 🙂

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Anonymous]

@KaranGupta 

Sorry for the chase. But it will be really great if you could mark the topic as resolved?

It will definitely help other Talend community members during their search in future and it is a way of showing thanks to the author for spending his time for your query in between their busy schedules.

Hope you understand 🙂

Warm Regards,

Nikhil Thampi

[Anonymous]

Hi Nikhil,

Thanks a lot for the help. I have marked it as a solution.

[palkondatharun]

i am working with the issues which you are explained in the topic please give solution for that issues.

its urgent i am dealing the project