SOLVED
Effective Date Filtering
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
levonbjohnson
6 - Meteoroid
‎05-27-2016
03:30 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I am trying to filter a database by most recent date in an effective date field, but I want the most recent date BY employee. Here is an example of what the database looks like (with the blue-font rows the ones I want returned):
Employee ID | Name | Job Title | Department | Effective Date | Action | Status |
504682 | Germain Ifedi | Analyst | Marketing | 9/1/2015 | Hire | Active |
504682 | Germain Ifedi | Analyst | Marketing | 4/15/2016 | Pay Raise | Active |
504682 | Germain Ifedi | Analyst II | Marketing | 5/1/2016 | Promotion | Active |
701665 | Jarran Reed | Specialist | Customer Service | 5/20/2016 | Hire | Active |
985222 | CJ Prosise | Analyst | Planning | 12/1/2013 | Hire | Active |
985222 | CJ Prosise | Analyst | Planning | 4/15/2014 | Pay Raise | Active |
985222 | CJ Prosise | Accountant | Finance | 11/3/2014 | Transfer | Active |
985222 | CJ Prosise | Accountant | Finance | 4/15/2015 | Pay Raise | Active |
985222 | CJ Prosise | Accountant | Finance | 4/30/2016 | Retirement | Inactive |
128761 | Nick Vannett | Specialist | Customer Service | 1/13/2016 | Hire | Active |
128761 | Nick Vannett | Specialist | Customer Service | 3/27/2016 | Termination | Inactive |
199744 | Rees Odhiambo | Sr Analyst | Planning | 8/19/2015 | Hire | Active |
199744 | Rees Odhiambo | Manager | Planning | 4/15/2016 | Pay Raise | Active |
Does anyone have any advice on how to create that filter?
Solved! Go to Solution.
Labels:
- Labels:
- Date Time
1 REPLY 1
MarqueeCrew
20 - Arcturus
‎05-27-2016
03:47 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
In-DB summarize: group by emp id , max eff date.
Join on these fields to your original data.
If you get duplicate data by emp id + eff date, you will either handle it in db or out.
Join on these fields to your original data.
If you get duplicate data by emp id + eff date, you will either handle it in db or out.
Alteryx ACE & Top Community Contributor
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.