Multifield Find and Replace possible?

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

Datasets

Developer Tools

Macros

Best Practices

Batch Macro

Dynamic Processing

Join

Interface Tools

Data Investigation

Accepted answers

Maskell_Rascal

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

New Workflow3.yxmd

All comments

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 : )

convert.yxmd

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.

Maskell_Rascal

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

New Workflow3.yxmd

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 🙂.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703