Bubbeling data

Here's an interesting problem I ran into last week:


Date                Code    Emplid Name of the Employee
2012/12/01 --  DTA -- 123 --  John
2010/12/10 -- XFR -- 123 -- John
2012/12/05 -- DTA -- 456 -- Bert
2012/05/08 -- DTA -- 567 -- Pascal
2000/06/04 -- DTA -- 567 -- Pascal
2000/05/01 -- XFR -- 567 -- Pascal



Right. My boss asked me, in the above list, to show him the transfers for people that got transferred, and for everyone else, he needed their most recent situation.

The tricky part is : you can't filter on a code and you can't filter on a date.

Part of the solution is of course to be found in the article where I showed you how to get the two most recent items. That is, we're going to turn our date into a number :

=(year([Date])*10000)+(monthnumberinyear([Date])*100) + daynumberinmonth([Date])

First, we need to create a break on Emplid of course.

CalcDate        Date             Code    Emplid Name of the Employee
20121201 -- 2012/12/01 --  DTA -- 123 --  John
20101210 -- 2010/12/10 -- XFR -- 123 -- John
20121205 -- 2012/12/05 -- DTA -- 456 -- Bert
20120508 -- 2012/05/08 -- DTA -- 567 -- Pascal
20000604 -- 2000/06/04 -- DTA -- 567 -- Pascal
20000501 -- 2000/05/01 -- XFR -- 567 -- Pascal


And then, we can create a rank on CalcDate and get our most recent items. Easy. BUT. There was an additional requirement. If the employee had a transfer, we need that person's most recent transfer.

Easy as pie :

=CalcDate * if([Code]="XFR") then (1000) else (1)

Of course, this messes up the date-look of Calcdate a bit, but it gets the job done. When you rank on it, you get :


CalcDate            Date             Code    Emplid Name of the Employee
20101210000 -- 2010/12/10 -- XFR -- 123 -- John
20121205       -- 2012/12/05 -- DTA -- 456 -- Bert
20000501000 -- 2000/05/01 -- XFR -- 567 -- Pascal

Mission accomplished :)

Cheers

Peter