Replace certain values and count number of records changed

[LinhNguyen]

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

TX

CO

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!

[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

[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!

[MichalM · Accepted solution]

@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

[LinhNguyen]

This is exactly what I was looking for. Thank you!