Find duplicates and overwrite field with highest value (but keeping both records)

Question

Hi,

I have a rather specific question and was not able to find a working solution with the other topics.

Background:

Basically I have some data with several identifiers (one unique) and I would like to look through the data and if there are any duplicates, overwrites the value in one specific column with the highest number. This probably sounds very confusing so I thought I would add an example below.

Example:

Unique IdentifierNumberNameValue112345Dog100212345Cat0

So I would look for duplicates with the "Number" column and would like to have this outcome:

Unique IdentifierNumberNameValue112345Dog100212345Cat100

So I would like Alteryx to not only sort the duplicates but actually overwrite one of the values (and do that for all cases with duplicates).

I was thinking about a formula but then got stuck writing it. I also saw versions with the unique tool and the summarize tool but they all replaced the duplicate rather than overwrote it.

Can anyone help me with this?

Much appreciated!

V

BrandonB · Accepted Answer

In the summarize tool, you can group by the number field and use the maximum function on each value that you want to check. Then you can join the output of the summarize tool back to your original data using the number field. Then you can use a formula that says IIF([original value] < [joined max value], [joined max value], [original value]) for the fields that you want to check.