Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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**

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- Aggregate Records in a table

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

george55

Partner - Creator III

2020-06-10
05:25 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:**

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 |

168 Views

1 Solution

Accepted Solutions

Or

MVP

2020-06-10
05:44 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:

161 Views

2 Replies

Or

MVP

2020-06-10
05:44 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:

162 Views

george55

Partner - Creator III

2020-06-10
06:47 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Thank you very much!

148 Views