community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

How do i remove duplicates values from list in each row

Meteor

Hi, 

I have a column and in each row i have a list e.g. row one will contain $apple;$orange;$orange, the next row will contain $pear;$strawberry, 

 

It's a semi colon separated list. How do I remove the repetitions in the list from each row?

Alteryx
Alteryx

You start by introducing a record id to identify each unique row. You will then use the semicolon to split the data into individual rows using Text to Columns and once ready, group the data by Record ID and the Value which will give you a unique set of values per record ID using the Summarize tool. You can then group it again by record ID, and concatenate the values using semicolon. See below (example attached).

 

concat.png

Meteor

thanks but when i use the text to columns how do i know what number of columns i should use? some have 20 in the list others 4 list in the items..

Alteryx Certified Partner

Hi @zsharif 

 

Here's a simple workflow that does the job:

 

Screenshot 2019-07-11 at 16.31.56.pngworkflowScreenshot 2019-07-11 at 16.30.46.pnginputScreenshot 2019-07-11 at 16.31.05.pngoutput

Alteryx Certified Partner

By splitting to rows you can deal with variance in the number of records per row (my approach and @MichalM 's approach both do this).

Alteryx
Alteryx

As @jamielaird says, if you open the example workflow I attached above you'll see it uses the split to row rather than column option

 

splitorows.png

It is possible to do in a single formula tool if you want:

 

REGEX_Replace([Input], "(\$[^;]+);(?=.*\1)", "")

 

This will replace all but the last instance of an item in the list. It assumes your items have a leading $ as the example you gave did.

 

 

Labels