Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
 
					
				
		
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
.png) 
					
				
		
 Miguel_Angel_Ba
		
			Miguel_Angel_BaHello,
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.
 
					
				
		
maybe this will help you
load
claim_no,
max(ver_no),
status
resident table_name
group by claim_no
 
					
				
		
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
 
					
				
		
Is there a way of doing it as an expression??
.png) 
					
				
		
 Miguel_Angel_Ba
		
			Miguel_Angel_BaHello,
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.
 
					
				
		
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.
