Keep max value over multiple rows

[bricz]

Hello all, 

I am working on cleaning some data, here's a sample: 

For each 'REFERENCE' I need to keep the lines that have the max 'Global_id'

Global_id	Reference	Database
521	1ae2	db.1
521	1ae2	db.2
600	1ae2	db.1
600	1ae2	db.2
321	TR56	db.1
321	TR57	db.2
605	1ae2	db.2
498	TR56	db.1
498	TR57	db.2

The result should look like this: 

Global_id	Reference	Database
605	1ae2	db.2
498	TR56	db.1
498	TR57	db.2

Any idea on how I can achieve this ? I initially wanted to create a custom function to process this data but it turns out custom functions don't really work as I expected in qlik Sense.

Thanks in advance 

[rwunderlich · Accepted solution]

Data:
LOAD Global_id,
     Reference,
     Database
FROM
[https://community.qlik.com/t5/Search-the-Community/Keep-max-value-over-multiple-rows/td-p/1884951]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

Inner Join(Data)
LOAD Max(Global_id) as Global_id,
     Reference
Resident Data
Group By Reference
; 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[bricz]

Thank you that worked perfectly !