Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
please find enclosed an example
regards
Bumin