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 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.
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 configuration
Text Input configuration