Replace Name in Multiple Fields

[shaheer]

Hello. I have a large set of data with many columns. I want to use the multi field formula tool to look for a specific case of a name, such as "The Rock" and change it to "Dwayne Johnson".

I already have an expression in a multi field formula that changes around names for me like so:

I want to add the formula to this tools expression if possible like so:

The reason why I want to do this is because when I add a second multi field formula tool right after the first one, I am unable to select any of the fields like I could in the first tool:

As you can see, in the second multi field formula tool, I am unable to select any fields. Any help would be appreciated.

[DavidSkaife]

Hi @shaheer 

You have the tool set to only Numeric (the default)

[DataNath]

@shaheer looking at your second screenshot, the reason these fields aren't appearing is because you have the Select X Fields: option set to numeric. If you go into that dropdown and choose 'All types of', or string etc then you'll be able to see your fields.

[shaheer]

Even when I set it to All types, none show up. 

[shaheer]

Nevermind, I just had to refresh and they appeared. Still, my if statement to change the current field is not correct:

IF [_CurrentField_]=="The Rock" THEN "Dwayne Johnson" ENDIF

How do I fix this?

[DataNath]

@shaheer are you wanting to conduct the replace on the name that comes out of the regex_replace formula? Or replace it before it goes into this? If it's the former, you can use a formula like:

IF REGEX_REPLACE(.......) = "The Rock" THEN "Dwayne Johnson" ELSE... ENDIF

[shaheer]

No, so the regex replace formula I have takes names that are in "[firstname], [lastname]" format and changes them to "[lastname] [firstname", so removing the comma and switching the names around.

After all of the names have been switched, I want to then change the case where a name is, for example, "The Rock" to "Dwayne Johnson".

[binuacs]

@shaheer Use the formula tool instead of Multi-Row tool to make the latest change you mentioned

[DataNath]

@shaheer ok that makes sense. In that case, you'd want to use a formula like I mentioned above, so it'd be:

IF <insert your regex_replace code here> = "The Rock" THEN "Dwayne Johnson" ELSE <insert your regex_replace code here> ENDIF

What this will do is tell Alteryx: if after doing the regex_replace, the result is "The Rock", change it to "Dwayne Johnson", otherwise leave it as the result of the regex_replace.

What I would say is that this approach is fine for one/two names. However, if you've got a bunch of names that you need to change then I'd recommend using a lookup table with the Find & Replace tool, rather than writing an enormous if statement.

[shaheer]

I guess I could, but I thought it would be easier to use this tool because in the formula tool I would have to add several formulas each related to a certain column.

Either way, my if statement is what the main problem is.