Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.
SOLVED

How to Remove Duplicate Pairs

Highlighted
6 - Meteoroid

How can I remove one of the two pairs (see highlighted text)?: 

 Capture.PNG

Thank you!

Highlighted
ACE Emeritus
ACE Emeritus

Use the Unique tool, and select both columns.

Hope that helps!

Highlighted
14 - Magnetar
14 - Magnetar

I believe that the unique tool will look for duplicate records in all of the fields you select, but it will look for duplicates in the fields separately. I do not think this will work for your case.

 

I have attached a workflow that should hopefully help. You would just want to keep what isn't joined in the Join tool (and then separate your fields again).

 

Let me know if this solves your problem!

Highlighted
6 - Meteoroid

Thank you both for your input!  I am actually looking to keep one of the two pairs.

 

If I start with:

Capture.PNG

 

I want to end with:

Capture2.PNG

 

I may be able to work off of this engine, though - this is a good start!

 

Thanks again.

Highlighted
14 - Magnetar
14 - Magnetar

@jrjoseph18 Sorry about that. I guess I didn't read your original post close enough. What about the attached module? I just added some tools after the Join to keep one of the duplicates. 

Highlighted
13 - Pulsar

You could also use Transpose and Summarize to deal with the duplicates:

 

image.png

 

ID each record, transpose so Field1 and Field 2 are in the same column, sort so your values are in the same order for each record, concatenate so each record is back on one row, grab a unique and use text to columns to separate the fields back out. Do a select at the end to drop unwanted columns and rename back to the original.

 

Note that this might flip-flop what's in Field1 vs Field2.  From your data it didn't look like this was important, but if it is then keep the RecordID in the Select tool and join it back to the data right after the Record ID tool.

 

 

Dan

Highlighted
ACE Emeritus
ACE Emeritus

My first response was clearly a bit hasty.  I would still approach this using the "Unique" tool, but I would create a new field "compareField" as follows...

Max(ToString([fld1]),ToString([fld2])) + "_" + 
Min(ToString([fld1]),ToString([fld2]))

... where [fld1] and [fld2] are the fields in question.  Then run Unique on "compareField".

 

Caveat: this doesn't extend beyond just two compare fields. But it's very fast for just two fields.

 

Highlighted
6 - Meteoroid

@BarnesK No problem - I appreciate your efforts, and for introducing me to the modulo function!  

Highlighted
6 - Meteoroid

@danrh - Thanks, Dan.  Reordering the pair ID's by adding a record ID, transposing the data, sorting the data by the record ID and pair ID's, then finally finding unique pair ID's is what helped me get what I needed.

Highlighted
7 - Meteor

Question: why are you using min and max? Seems like useless processing overhead?  please explain.

Labels