Alteryx Designer

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

Remove Unique Values and Keep Duplicates

Highlighted
11 - Bolide

I would like to remove unique IDs and keep duplicates.

 

e.g.)

IDName
1Apple
1Apple
2Banana
3Watermelon
3Watermelon
4Strawberry
5Peach

 

Remove unique IDs, but keep duplicate IDs.

 

IDName
1Apple
1Apple
3Watermelon
3Watermelon

 

Does anyone know how to approach this?

Thank you!

 

Kazumi

Highlighted
Alteryx Certified Partner

Hi Kazumi,

 

One way to do this would be to get a count of each ID with the summarize tool and then filter on that count. I've attached an example workflow.

 

roadhouse

Highlighted
16 - Nebula
16 - Nebula

An alternative solution is to use a Unique tool and take the duplicate output. In order to avoid duplications you will need to unique this set.

 

You can then join this back to the original input using a Join tool and then you will get all the records you want.

 

Sample attached.

 

Summarize as suggested by @roadhouse is probably simpler

Highlighted
11 - Bolide

Hi roadhouse,

 

Thank you for your quick reply.

I agree that your method separates unique and duplicate values. However, the duplicated value is now combined to one row.

Do you think we can keep the duplicated values in separate rows?

 

e.g.)

IDName

1

Apple
1Apple
3Watermelon
3Watermelon

 

Thank you,

Kazumi

Highlighted
11 - Bolide

Hi jdunkerley79,

 

Thank you for your reply.

This is exactly what I was looking for.

 

Kazumi

 

 

Highlighted
Alteryx Certified Partner

@knozawa wrote:

Hi roadhouse,

 

Thank you for your quick reply.

I agree that your method separates unique and duplicate values. However, the duplicated value is now combined to one row.

Do you think we can keep the duplicated values in separate rows?

 

e.g.)

IDName

1

Apple
1Apple
3Watermelon
3Watermelon

 

Thank you,

Kazumi



Sure, use the Generate Rows tool to add new rows based on count. Updated workflow is attached.

 

roadhouse

Highlighted
11 - Bolide

Hi roadhouse,

 

Thank you! This is very useful.

 

Kazumi

Highlighted
Alteryx Alumni (Retired)

One other option for this is to use the Only Unique macro from Adam Riely's Crew Macro pack. He released an update/expanded one back in July 2016. Well worth a look if you haven't seen them before!

 

only unique.jpg

Highlighted
11 - Bolide

Hi RusselID,

 

Thank you for your suggestion! I just downloaded the package.

 

Kazumi

Labels