Hi everyone! I'm working on replacing some values in a column and get the number of records changed. For example if in column A, there's "CO", I want to change it to Colorado, if there's "TX", it should become "Texas", etc. (there're 5 values like that). However, there are a lot of other values in that column that I want to keep the same. Column ATXCOIllinoisLos AngelesNew York The problem is: the dynamic replacement tool gives me the count but it turns all other values to null. The normal replacement tool doesn't give me a count. Is there any way I can get the output that I need without using a filter and count tool in the original data? Thank you!

Replace certain values and count number of records changed

Hi everyone!

I'm working on replacing some values in a column and get the number of records changed.

For example if in column A, there's "CO", I want to change it to Colorado, if there's "TX", it should become "Texas", etc. (there're 5 values like that). However, there are a lot of other values in that column that I want to keep the same.

Column A

Illinois

Los Angeles

New York

The problem is: the dynamic replacement tool gives me the count but it turns all other values to null. The normal replacement tool doesn't give me a count.

Is there any way I can get the output that I need without using a filter and count tool in the original data?

Thank you!

Transformation

Accepted answers

MichalM

@LinhNguyen

In this case you need to include a condition which tells Alteryx what to do if the lookup value is not in the set which in this case would be keep the value as is - [Column A]

And make sure you check the 'Values are Expressions/Formulas' box

dynamic-replace.yxmd

All comments

AndrewBanh

Hi @LinhNguyen

Is there a particular reason why you need to use the dynamic replace tool in particular?

I found it easiest to use a Find and Replace tool (as in attached workflow). How did you want the count to appear? Did you want a new column which flags records which have been changed or do you want an output which just sums it up?

- Andrew

Find Replace.yxmd

LinhNguyen

Hi Andrew,

I don't really have to use the dynamic replace tool. The only problem is that I'll need to replace twice in my workflow. The dynamic replace tool can give me a count of values that were replaced. The find & replace tool is more straight forward but it doesn't have the count function and I will need to count with other tools. Plus, the more I add the slower it will be.

I'm trying to find the best way to do this since my workflow is pretty complex already and I don't want to add 4-5 more just to replace and count. I will also need to give this workflow to my teammates so keeping it short and easy to understand for them is one of my priority.

Thank you!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703