Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

Verenala4
8 - Asteroid

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 IdentifierNumberNameValue
112345Dog100
212345Cat0

 

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

Unique IdentifierNumberNameValue
112345Dog100
212345Cat100

 

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

2 REPLIES 2
BrandonB
Alteryx
Alteryx

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
8 - Asteroid

Thank you @BrandonB !

 

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

 

V

Labels