topic Re: See All records that are duplicates, including the original unique record in Alteryx Designer Desktop Discussions

See All records that are duplicates, including the original unique record

InfoManager — Fri, 08 Feb 2019 05:04:54 GMT

I'd like to see all rows that have duplicate values in the same column. But, I don't want to just see the duplicates (usually using the Unique function) but see the the original unique one, and the duplicates thereafter. Afterwards I would have two data sets, one with content that is never duplicated in the data, and a data set of all content that is duplicated, including the original.

How would I do this? Thanks!

Re: See All records that are duplicates, including the original unique record

cmcclellan — Fri, 08 Feb 2019 07:53:45 GMT

I would still recommend using the Unique tool. It gives you the first unique on one output and the rest of the duplicates on the other output. You can then join together to get all the records ... or just add tools from before the Unique tool

Re: See All records that are duplicates, including the original unique record

DavidP — Fri, 08 Feb 2019 09:25:24 GMT

I like @cmcclellan's idea. Here's how I'd do it

Re: See All records that are duplicates, including the original unique record

DavidP — Fri, 08 Feb 2019 16:05:04 GMT

I knew there was a macro out there that does exactly what you want!

http://www.chaosreignswithin.com/2014/08/only-unique-macro.html

Re: See All records that are duplicates, including the original unique record

InfoManager — Mon, 11 Feb 2019 21:38:01 GMT

Thanks, DavidP. I think I might still be missing something. The last filter that gets me all the dupes, doesn't get me the original unique row that is later duplicated since only the duplicated rows have the "y" in the last column. I wonder if I just remove the initial Unique tool and just start with the Group by function, if that would solve the problem...I think I would then be able to get ride of the join and union...

Re: See All records that are duplicates, including the original unique record

InfoManager — Mon, 11 Feb 2019 21:42:28 GMT

Nope! That didn't work. I'll keep playing with it. I'm definitely missing something...

Re: See All records that are duplicates, including the original unique record

DavidP — Mon, 11 Feb 2019 22:48:44 GMT

Can you please send me some test data?

If you look at the J output of the Join tool, it should show all the Dup records, including the Original unique row - these records all have "y" flag. The records at the L output are the ones for which there are no dups. The union tool brings then together in one dataset again.

Come to think of it, the L and J outputs of the Join tool gives you the same output as the filter tool, so you don't need the filter and union tools.

Did you try the Crew (Only unique) Macro?

Re: See All records that are duplicates, including the original unique record

estherb47 — Tue, 12 Feb 2019 01:26:35 GMT

Hi @InfoManager

I'd use the Crew Macro's Only Unique tool, if you're able to download. See @DavidP 's post for the link.

But, if you need you can approximate that tool using some Unique, Joins, Summarizes. By Joining the U output back to the D output of Unique, you'll get all of the truly unique (no dupes) out of the L output. Then, easily repeat the first instance of each duplicate using the Summarize (you could also sample, grouping on all of the fields and setting N to 1). Added a field to qualify as original (value of 1) or copy (value of 2). Unioned and sorted.

The Only Unique macro does all of this for you. It's fantastic!

Re: See All records that are duplicates, including the original unique record

Tommy1 — Mon, 24 Aug 2020 16:19:23 GMT

I had this issue and simply used the Unique tool, a formula, and Union