Renaming a field based on whether it contains a certain word

[wht822]

Hello,

I'm trying to find a way to change the name of a field based on whether it contains a certain word. For example, if F1 contains the word "quantity" then I want to rename the field to quantity. If not, then I want to ask if F1 contains the word "price." If it does, then I want it to be renamed "price."

Currently, I'm using dynamic rename and entering the following expression:

IF "Quantity" THEN "Quantity" ELSEIF "Price" THEN "Price" ELSE "0" ENDIF

I've tried using IF Contains in the tool, but it's been unsuccessful. Any help would be appreciated!

[fmvizcaino]

Hi @wht822 ,

Take a look at the attached example. I'm using the same as you, only needed to adjust a bit the formula.

Let me know if that works for you.

Best,

Fernando Vizcaino

[jrgo · Accepted solution]

Hi @wht822 

Based off what you explained you attempted to do with the Dynamic Rename tool, I'm guessing what you're trying to do is rename a field if the ROW DATA contains that word. If correct, your attempt will not work as that tool only evaluates the headers, not the data within.

Take a look at the example attached to see one way to change a column name based on matching something within the data.

Jimmy
Teknion Data Solutions

[wht822]

Fernando, thanks for the reply! Unfortunately, for some reason or another, the solution didn't work. The dataset I'm using does contain many null values as well as random types of data in each field (addresses, names, dates, account numbers, etc). Would this have any bearing on the effectiveness of the formula?

[fmvizcaino]

Hi @wht822 ,

I have created a way to rename the field if the header contains a part of the string. But if you need to check if any row of your data contain that specific string in order to rename the column, the solution @jrgo does exactly what you need.

Best,

Fernando V.