Select First record of one group and last record of second group

Six Stars

Select First record of one group and last record of second group

Hi Guys I am actually trying to create a Taland map to remove duplicates from an attendance system. I have been able to identify the duplicates in the attendance system but I need to pick the first duplicate record in case of an entry to the office and the last duplicate record in case of exit from the office. I have been able to sort and get the data in the format as displayed below. 

 

I need to pick the 2nd row as that is an entry and I need to pick the 5th Row as that is the last exit for that employee for that day this is for employee AA for 08-01-2018

 

For the Date 08-02-2018 for AA I need to have all the records as there are no duplicate Entry or Exit Entries.

 

For Date 08-01-2018 for BB I need to take the 10th and the 12th record

 

So to Simplify it  I have also attached the desired out put.

 

 

 

 

Employee

Re: Select First record of one group and last record of second group

Hi,

 

    Could you please provide the sample file as a csv attachment?

 

Warm Regards,

 

Nikhil Thampi

Six Stars

Re: Select First record of one group and last record of second group

Attached is the file

Seven Stars

Re: Select First record of one group and last record of second group

Hello,

 

1) taggregaterow by grouping on IO_Date_Only & Emp_name and output operation of IO_Status,IO_time, flag with function as First and other taggregaterow  with function as last.

2) Using tunite component for first & last record and then tsortrow to sort in ascending order.

 

records.PNG

 

Hope this solves the purpose..!!

Regards

Ganshyam Patel

Six Stars

Re: Select First record of one group and last record of second group

Sorry for the confusion. My mistake. I should have shared a better example. I am reattaching a fresh file to better explain the scenario.

 

For records dated 08-02-2018 I should get all the records in the output because there is no consecutive entries or exits.

Six Stars

Re: Select First record of one group and last record of second group

Any updates ?!!!

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now