Multifield Find and Replace possible?

[horse9118]

Hello, 

I have a lot of files in different formats and I'm trying to normalize everything into 1 format. Part of that is to convert all the different categorical values for the same field. Essentially if a field has categorical values of "A","B","C", etc, they should be converted to "1", "2", "3", etc. I know that I can use the Find & Replace tool along with a lookup table to do the conversion, but I have to do this for about 20 fields with different lookups values. Other than using the Find & Replace tool 20 times, is there a more efficient way to do this? 

For instance I can stack the look up tables in 3 columns, 

Field_Name, Orig_Code, Normalized_Code

Field 1, C, 1

Field 2, F, 1  

Field name would be the actual field that needs to be normalized, and Orig_Code would then be replaced with Normalized code for that field only. 

Anyway this can be done in Alteryx? 

Thanks

[atcodedog05]

Hi @horse9118 

You can use Multi-field formula tool and switch formula. Here is how you can do it.

Input:

Workflow:

Hope this helps : )

[horse9118]

@atcodedog05 Thanks for the reply. This doesn't work for for me because the all the different fields have different codes. For example 1 field could be loan type and other could be ARM Index so its not possible capture everything in 1 switch statement. Also I want the ability to update the lookup table outside of the workflow instead of hard coding it with the switch statement. 

[atcodedog05]

Hi @horse9118 

If different fields have different codes you need to use multiple find and replace tool for each.

[horse9118]

Yea, I'm trying to avoid that if possible.

[Maskell_Rascal · Accepted solution]

Hi @horse9118 

Correct me if I'm wrong here, but I believe you can use a transpose/crosstab with a join to accomplish this. 

Input:

Workflow:

Let me know if that works. 

Cheers!

Phil

[horse9118]

@Maskell_Rascal This works great. Thanks! 

[atcodedog05]

Hi @horse9118 

Just out of curiosity if the find and replace method works shouldn't the Multi-field approach should also work they are doing the same thing. 

Just curious to understand where was Multi-field approach failing and Find & replace succeeding just to improve my understanding 🙂.