Return Last Text String When Aggregating

[#1]

Hi

i have this table

Claim no	Ver No	Status
LD000111	1	Open
LD000111	2	Closed
LD000111	3	Closed
Ld000222	1	Closed
Ld000222	2	Open
Ld000222	3	Open

I wish to aggregate into the below table by returning the MAX version number (Which i have) and the corresponding Text field. how do i get the corresponding text field??

Claim no	Ver No	Status
LD000111	3	Closed
Ld000222	3	Open

Many Thanks

[#2]

maybe this will help you

load
claim_no,
max(ver_no),
status
resident table_name
group by claim_no

[#3]

This is a two step operation.

First Agregate the data to get the last version.

Aggr_table:

load claim_no,

max(ver_no) as maxverno,

resident table_name

group by claim_no;

then :

left join(Aggr_table)

load claim_no,

ver_no as maxverno,

status

resident table_name;

hope that helps.

Philippe

[#4]

Is there a way of doing it as an expression??

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

Use [Claim No] and [Ver No] as dimension and the following as expression:

Only(If(Aggr(NODISTINCT Max([Ver No]), [Claim no]) = [Ver No], Status))

Hope that helps.

[#6]

I would say:

aggr(if([Ver No]=max(total <[Claim no]> [Ver No]),[Status]),[Claim No],[Ver No])

Probably the same result as what the guy above me said but I can't see it as I'm typing this.