topic Re: Keep max value over multiple rows in Search the Community

Keep max value over multiple rows

bricz — Tue, 25 Jan 2022 11:01:26 GMT

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

Re: Keep max value over multiple rows

rwunderlich — Tue, 25 Jan 2022 19:14:40 GMT

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

Re: Keep max value over multiple rows

bricz — Wed, 26 Jan 2022 11:06:32 GMT

Thank you that worked perfectly !