If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
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 ContractID2) 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
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:
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, Col51, , a, b, c2, 20, a, b, c3, 50, a, b, c3, 55, a, b, c4, 50, a, b, c4, ,a, b, c];
View solution in original post
wow, the solution was very easy: Aggr(Max(Age),ContractID)
Thank you very much!