Distinct Join based on most recent date

Question

Hello All,

I have a workflow where I'm taking employee HR data and joining it with a user access listing (UAL) for a specific system. The problem is the HR data will have duplicate records if an employee left and was rehired, so it will have their original position with a hire and term date and then a new record with their new position and new hire date. Their employee ID is the same for both records and the UAL does not have the hire dates. I'm doing a join on the employee ID but am getting duplicates for rehires. How can I do the join so it only returns the record with the most recent hire date? Or how could I filter (or whatever function I would use) the HR data to remove duplicate records and only keep the record with the most recent hire date? Thanks.

fmvizcaino · Accepted Answer

Hi @elclark ,

You could use a sort tool to order your HR dataset and use a sample tool to get only the first record for each employee ID, then you will have a dataset with only the newest records for each employee ID.

Does that make sense to you?

Best,

Fernando Vizcaino

EW · Accepted Answer

If I'm understanding correctly, one way would be to sort by hire dates (descending) and then use the unique tool prior to the join, like the attached.  If that's not what you meant, could you attach sample data?