Aggregate Records in a table

[george55]

Hi all,

Have a table with a lots of columns.
In Column 'Age' there are some ones without an age.
Column 'ContractID' can have multiples customers. In such case: Only 'Age' differs, rest have the same columns.

Goal:
1) Get a table with only 1 record per ContractID
2) If an 'Age' is available, take ony record with the highest 'Age'

Tried something like that, but did not work:
=If(Isnum(Age) = -1, Max(Aggr(Max(Age), ContractID)), ContractID)

Question her is also, where do I have to put the expression. In the 'ContractID' or in the 'Age' column?
Thank you for your help

Actual state:

ContractID	Age	Col3	Col4	Col5
1		a	b	c
2	20	a	b	c
3	50	a	b	c
3	55	a	b	c
4	50	a	b	c
4		a	b	c

Desired Output:

ContractID	Age	Col3	Col4	Col5
1		a	b	c
2	20	a	b	c
3	55	a	b	c
4	50	a	b	c

[Or · Accepted solution]

Ideally you would do this in script, by loading ContractID, Column1, Column2, ..., max(Age) From Table1 GROUP BY ContractID.

If you need to do this in the presentation layer, the ideal way would be to have multiple dimensions, and one measure of Max(Age).

If you for some reason need this to be a dimension, it should be:

Aggr(Max(Age),ContractID)

Script used (added condition to change missing ages to null):

Load ContractID, if(len(Age)>0,Age) as Age, Col3, Col4, Col5 Inline [
ContractID, Age, Col3, Col4, Col5
1, , a, b, c
2, 20, a, b, c
3, 50, a, b, c
3, 55, a, b, c
4, 50, a, b, c
4, ,a, b, c
];

Result:

[george55]

wow, the solution was very easy: Aggr(Max(Age),ContractID)

Thank you very much!