Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Return Last Text String When Aggregating

Hi

i have this table

Claim noVer NoStatus
LD0001111Open
LD0001112Closed
LD0001113Closed
Ld0002221Closed
Ld0002222Open
Ld0002223Open


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 noVer NoStatus
LD0001113Closed
Ld0002223Open


Many Thanks

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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.

View solution in original post

5 Replies
Not applicable
Author

maybe this will help you


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


Not applicable
Author

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

Not applicable
Author

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

Miguel_Angel_Baeyens

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.

Not applicable
Author

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.