topic Re: How to only load in dimensional data with highest date in QlikView

How to only load in dimensional data with highest date

Thu, 03 Jan 2013 14:28:24 GMT

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

Re: How to only load in dimensional data with highest date

Thu, 03 Jan 2013 15:28:29 GMT

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

Re: How to only load in dimensional data with highest date

bumin — Thu, 03 Jan 2013 15:39:22 GMT

Hi,

please find enclosed an example

regards

Bumin