Is there way to standardize based on variable input (contains tool?)

Hello.

I am processing data that may have variations in the how a value is represented. For ex. High, High Severity, Moderate-High, Mod-High, high, HIGH in the filter tool as there is already complex logic, is it possible to do something like this in a formula tool prior to the filters (there are 10) that evaluate the records (can be in more than 1 filter). Need something like looks for a case-insensitive appears of the word high and then set the value in the field to HIGH.

I tried the contains tool but couldn't get it to work.

Thanks in advance,

Expression

Accepted answers

rfoster7

if contains(uppercase([field],'HIGH')) then 'HIGH' else <addl logic here for other classifciations> endif

you would have to uppercase it first, then do the contains.

Alternatively, you could look at the fuzzy match tool to see if you can find a fuzzy logic that best delineates your data.

All comments

rfoster7

if contains(uppercase([field],'HIGH')) then 'HIGH' else <addl logic here for other classifciations> endif

you would have to uppercase it first, then do the contains.

Alternatively, you could look at the fuzzy match tool to see if you can find a fuzzy logic that best delineates your data.

Raj

Also, if there is a specific list of data you can use find and replace tool as there is an option for case insensitivity in it.
the solution can be achieved through multiple ways, you can use any which is suitable for you.

kimc745

When I try to use contains it results in an error because I am attempting to use string on a numeric. my field is string but the value shows as either a 0 or -1 on the field.

Raj

@kimc745 You are Creating new Column or updating the same column?

if you are updating same column add an select tool to make it string first.

kimc745

Hi @Raj

Yes, the column can have the different values (it is VString or VWString). Since there is no standardization on how the word High can appear, I just need to look to see if string has High in it (ex. mod-high, moderate high, High, etc.) and convert it all to High. I'd like to do it in the formula there is 10 different metrics which have different filters. The result can appear in 1 or more. I'd like to assign it to same metric. Just having an issue getting it to look at the different variants possible.

Thanks

Kimberly

kimc745

@rfoster7 @Raj

I combined the suggestions and used formula with basic contains and case insensitivity. I had expected a true / false result to be 1 (True) and 0 (False) but it returned -1. So I created a helper variable as numeric and then put logic in the original column that if -1 then "high" so it will take anything that carries contains regardless of case or spacing or hyphens. Tested a few iterations of data values and seems to be working. Thanks for the assist.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335