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

[Verenala4]

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 Identifier	Number	Name	Value
1	12345	Dog	100
2	12345	Cat	0

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

Unique Identifier	Number	Name	Value
1	12345	Dog	100
2	12345	Cat	100

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 solution]

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. 

[Verenala4]

Thank you @BrandonB !

This worked perfectly and was easy enough to add to my existing workflow!

V