Find and Replace in Alteryx: Add a new field with matching values

by Naledi Hollbruegge

I recently had a look at some data from the 2016 elections for the new Mayor of London. One of these files provided the name of the candidate but not the party. I used the Find Replace tool in order to append these to the file for every mention of the candidate.

Data

Data example

One way to do this is to create a formula tool with an IF statement, where you look up one string in one field and then add a different string in your new field. This is the approach I took earlier on in the worflow, as each candidate was marked only by a number rather than their name. So I assigned a candidate to each number to replace these.

IF [First Vote]=’1′ THEN ‘Sian Rebecca Berry’
ELSEIF [First Vote]=’2′ THEN ‘David Furness’
ELSEIF [First Vote]=’3′ THEN ‘George Galloway’
ELSEIF [First Vote]=’4′ THEN ‘Paul Golding’
ELSEIF [First Vote]=’5′ THEN ‘Zac Goldsmith’
ELSEIF [First Vote]=’6′ THEN ‘Lee Eli Harris’
ELSEIF [First Vote]=’7′ THEN ‘Sadiq Aman Khan’
ELSEIF [First Vote]=’8′ THEN ‘Ankit Love’
ELSEIF [First Vote]=’9′ THEN ‘Caroline Valerie Pidgeon’
ELSEIF [First Vote]=’10’ THEN ‘Sophie Walker’
ELSEIF [First Vote]=’11’ THEN ‘Peter Robin Whittle’
ELSEIF [First Vote]=’12’ THEN ‘Prince Zylinski’
ELSE ‘0’
ENDIF

This is fairly easy if you have only a few things you want to replace but it can become a very  complex If statement if there are a lot of replacements to be done. Through the use of a Text Input and Find Replace this can be made a lot simpler and I had to assign parties twice so in the second case I used the Find Replace tools.

Find replace tools in workflow

Workflow section example

I didn’t want to type out the whole candidate name exactly so I just made one column with the last name of the candidate and set the tool to find “Any part of the field”. I then specified the fields that matched between the complete file and the input tool. As I didn’t want to replace anything in the file I chose to append the fields to the record rather than replacing text within a field. The result is a new column that matches the party to the candidate the first vote went to. With just a few changes for the second Find Replace tool this process was repeated for the candidate the second vote went to.

 

Find Replace

Find Replace configuration

Text Input

Text Input configuration

 

Avatar

Naledi Hollbruegge

Custom Geocoding in Tableau: Mapping London Tube Stops

3 mins read

Thu 25 Aug 2016

Tableau Tip Tuesday: Hiding the null indicator

2 mins read

Tue 23 Aug 2016

Alteryx Quick Tip: Find your tool number

2 mins read

Mon 22 Aug 2016

Making an app with the List Box tool in Alteryx

3 mins read

Mon 15 Aug 2016