Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Effective Date Filtering

levonbjohnson
6 - Meteoroid

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 IDNameJob TitleDepartmentEffective DateActionStatus
504682Germain IfediAnalystMarketing9/1/2015HireActive
504682Germain IfediAnalystMarketing4/15/2016Pay RaiseActive
504682Germain IfediAnalyst IIMarketing5/1/2016PromotionActive
701665Jarran ReedSpecialistCustomer Service5/20/2016HireActive
985222CJ ProsiseAnalystPlanning12/1/2013HireActive
985222CJ ProsiseAnalystPlanning4/15/2014Pay RaiseActive
985222CJ ProsiseAccountantFinance11/3/2014TransferActive
985222CJ ProsiseAccountantFinance4/15/2015Pay RaiseActive
985222CJ ProsiseAccountantFinance4/30/2016RetirementInactive
128761Nick VannettSpecialistCustomer Service1/13/2016HireActive
128761Nick VannettSpecialistCustomer Service3/27/2016TerminationInactive
199744Rees OdhiamboSr AnalystPlanning8/19/2015HireActive
199744Rees OdhiamboManagerPlanning4/15/2016Pay RaiseActive

 

Does anyone have any advice on how to create that filter?

1 REPLY 1
MarqueeCrew
20 - Arcturus
20 - Arcturus
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.
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Labels