FirstSortedValue

[Anonymous]

Hello All,

My requirement is to get only the records highlighted in yellow with the bold font as in the image attached.

Essentially if a record is amended the its ID get updated i.e ; followed by the number of times.So in such instances I only want the most recent update. thus the expression ;

=FIRSTSORTEDVALUE(AGGR(SUM(DISTINCT AMOUNT),CLIENT,ID)
,
-AGGR(SUM([AMENDMENT NUMBER]),CLIENT,ID)) where Amendment number is derived as MID(ID,14,3)) AS [AMENDMENT NUMBER].

But unfortunately this fails to work for client's with no amendment like Moses, Victor just a few.

Please help, Thanks

[sunny_talwar]

Try this:

=FirstSortedValue(Aggr(Sum(DISTINCT AMOUNT) ,CLIENT), -Aggr([AMENDMENT NUMBER], CLIENT))

[swuehl]

Try

Subfield(ID &';1' , ';' ,2) AS [AMENDMENT NUMBER]

[sunny_talwar]

This is very smart Stefan . I have not tested this, but this might work also?

Alt(Mid(ID,14,3)), 1) as [AMENDMENT NUMBER]

[Anonymous]

Thanks Guys for the quick response, I also learnt something new.

However a client like Adeliade should show 3 records with a total amount of 450,252.00 and not 200,128.00

ADELIADE	TF1634818035	200,128.00	1,603.93	1	09/01/2017
ADELIADE	TF1634890173	50,068.00	214.50	1	09/01/2017
ADELIADE	TF1634898830	200,056.00	1,603.55	1	09/01/2017

[sunny_talwar]

May be you need a new ID field which is just like this:

LOAD Left(ID, 12) as New_ID

and then this:

=FirstSortedValue(Aggr(Sum(DISTINCT AMOUNT), CLIENT, New_ID), -Aggr([AMENDMENT NUMBER], CLIENT, New_ID))

[Anonymous]

Hi Sunny,

Tried that but but did not avail....

[sunny_talwar]

Would you be able to share your qvw or a sample?

[Anonymous]

Your email please?

[sunny_talwar]

I have added you as a connection and you should be able to send me a private message (PM). I can share my id on PM.