How to only load in dimensional data with highest date

[#1]

I have the following data:

Client Number	Advisor	Date
BN1016872	abanc	07 2012
BN1016872	ananv	12 2012
BN154879	abanc	01 2012
BN154879	ananv	05 2012
BN154879	adafg	11 2012

and I want to end up with a mapping table that gives me the client number and the advisor for the most recent date (ie i just want one line per client).  The data i want to end up with is below

Client Number	Advisor	Date
BN1016872	ananv	12 2012
BN154879	adafg	11 2012

I can't figure out a way to do this, i have tried using groupby but i just get the same data as it gives me the max date for each change in advisor.  The problem i have is that an advisor can have many clients.

Has anyone got any ideas

THanks

[#2]

Hey,

You could try something like this. Make sure the table is sorted in the way you want it. In your case by advisor ascending  and Date descending.

Then create a field which indicates if it's the first row for the advisor. Then select only those rows which are the first row for the advisor.

Data2:

NoConcatenate Load *

Resident Data

order by Advisor,Datum desc;

;

drop tables Data;

Data3:

NoConcatenate load *,

if(RowNo()=1,1,

if(peek(Advisor,-1)=Advisor,0,1))                                        as hlp

Resident Data2;

drop tables Data2;

Data4:

NoConcatenate Load

          Client_Number,

          Advisor,

          Datum

Resident Data3

where hlp=1;

drop tables Data3;

There is probably a more efficient solution to do this, but I guess this one will work in your case.

Hope this helps.

Gr.

Frank

[bumin]

Hi,

please find enclosed an example

regards

Bumin