Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
george55
Partner - Creator III
Partner - Creator III

Aggregate Records in a table

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:

ContractIDAgeCol3Col4Col5
1 abc
220abc
350abc
355abc
450abc
4 abc

 

Desired Output:

ContractIDAgeCol3Col4Col5
1 abc
220abc
355abc
450abc
Labels (1)
  • SaaS

1 Solution

Accepted Solutions
Or
MVP
MVP

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:

Or_0-1591782336422.png

 

View solution in original post

2 Replies
Or
MVP
MVP

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:

Or_0-1591782336422.png

 

george55
Partner - Creator III
Partner - Creator III
Author

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

Thank you very much!