topic Re. :Re: Return Last Text String When Aggregating in QlikView

Return Last Text String When Aggregating

Wed, 13 Apr 2011 11:30:17 GMT

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

Return Last Text String When Aggregating

Wed, 13 Apr 2011 11:51:16 GMT

maybe this will help you


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

Re. :Re: Return Last Text String When Aggregating

Wed, 13 Apr 2011 14:03:06 GMT

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

Re. :Re: Return Last Text String When Aggregating

Wed, 13 Apr 2011 14:26:31 GMT

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

Re. :Re: Return Last Text String When Aggregating

Miguel_Angel_Baeyens — Wed, 13 Apr 2011 14:37:04 GMT

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.

Return Last Text String When Aggregating

Wed, 13 Apr 2011 14:50:12 GMT

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.