Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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:
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:
wow, the solution was very easy: Aggr(Max(Age),ContractID)
Thank you very much!