Separate all uniques and all duplicates

DanWhalen

The Unique tool separates all unique records into one stream, and all the "extras" into a separate one.

INPUT:
ColName
cat
cat
dog

OUTPUT:
UNIQUE
ColName
cat
dog

DUPLICATE
ColName
cat

But what if I want instead for all the Uniques to go to one data steam and all Duplicates to go to the other?

OUTPUT:
UNIQUE
ColName
dog

DUPLICATE
ColName
cat
cat

I can imagine some workarounds, but is there a preferred way of accomplishing this?

Join

Accepted answers

jdunkerley79

Use a summarise tool (Group By ColName, Count ColName) and then filter when Count = 1

This will give you unique records

You can then join this back to the original and the J output will have the unique records, L output will have the others

Only Unique.yxmd

All comments

MarqueeCrew

Try watching my YouTube video which explains the only unique tool.

https://youtu.be/HXhpgEOSi2Q

Cheers,

Mark

ZacharyM

+1 to what @MarqueeCrew said - the CrEW macros are amazing and I highly recommend them, for this tool and others in the package!

jdunkerley79

Use a summarise tool (Group By ColName, Count ColName) and then filter when Count = 1

This will give you unique records

You can then join this back to the original and the J output will have the unique records, L output will have the others

Only Unique.yxmd

DanWhalen

This is exactly what I was looking for, thanks!

DanWhalen

Mark,

I appreciate the macro, but I'd like to avoid them at the moment. I'm new to Alteryx, and for now, I want to make sure I'm able to understand/articulate all of what my code is doing. Haven't gotten into macros yet, would have to treat it as a black box right now, and I feel hinky about black boxes this early on. Trying to stick to the basics only right now.

Soon though!

Thank you, I appreciate the reply!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703