I have two data sets. One is a list of employee IDs active in the current year and the history of any changes (EID). The other is a listing of PRs by data and creator (PR). What I want to do is make the name and title in EID with the date and creator in PR. So, for example, if I have
EID | ||||
EmplID | Reason | Effective Date | Title | Name |
1001 | Hire | 1/5/2002 | Handler | Doug |
1001 | Promote | 6/2/2019 | Head Handler | Doug |
1001 | Pay Increase | 9/30/2019 | Head Handler | Doug |
1002 | Pay Increase | 1/1/2019 | Lead Engineer | Sue |
1002 | Pay Increase | 7/1/2019 | Lead Engineer | Sue |
1003 | Pay Increase | 5/7/2018 | Maintance Tech | Pattie |
1003 | Promote | 4/20/2019 | Lead Tech | Pattie |
1003 | Term | 12/31/2019 | Retiree | Pattie
|
PR | |
Date | Creator |
1/15/2019 | 1001 |
3/3/2019 | 1002 |
4/19/2019 | 1003 |
6/5/2019 | 1003 |
7/22/2019 | 1002 |
9/7/2019 | 1001 |
10/24/2019 | 1001 |
12/10/2019 | 1002 |
The result would be
Date | Creator | Name | Title |
1/15/2019 | 1001 | Doug | Handler |
3/3/2019 | 1002 | Sue | Lead Engineer |
4/19/2019 | 1003 | Pattie | Maintance Tech |
6/5/2019 | 1003 | Pattie | Lead Tech |
7/22/2019 | 1002 | Sue | Lead Engineer |
9/7/2019 | 1001 | Doug | Head Handler |
10/24/2019 | 1001 | Doug | Head Handler |
12/10/2019 | 1002 | Sue | Lead Engineer |
There are a number of dates, changes, demotions, etc in the EID and I am having trouble sorting through the noise.
How can I match up the right name and title with the PR date?
Solved! Go to Solution.
@DylanBain - take a look at the attached sample solution.Let us know if this helps.
Hi @DylanBain I mocked up a workflow that produces the output you describe let me know what you think?
That did the trick! Thanks!