RegEx Replace?

[bsolove]

I did some transposing and some of my fields got messed up.

I have a column called type which contains records called Property, Payroll and Sales.

If my Description Column = "Average CY Property Den + Rent" or "Average CY Property Num + Rent" or "Average Property Den" or "Average Property Num" or "CY Property Den" or "CY Property Num" or "CY Rental Den" or "CY Rental Num" or "CY Rental x 8 Den" or "CY Rental x 8 Num" or "PY Property Den" or "PY Property Num" then I want Type to be Property.

If my Description Column = "CY Payroll Den" or "CY Payroll Num" then I want Type to be Payroll.

If my Description Column = "CY Sales Den" or "CY Sales Num" then I want Type to be Sales.

I tried doing three different If statements in the same formula tool, but did not get the desired results.

Thanks in advance!

[JohnJPS]

Hi @bsolove,

There should be a number of approaches...

SWITCH(FLOOR([DescriptionColumn]), "Unknown",
"CY Payroll Den", "Payroll",
"CY Payroll Num, "Payroll",
"CY Sales Den", "Sales",
"CY Sales Num","Sales")

... or ...

IIF(Contains([DescriptionColumn], "Payroll"), "Payroll",
   IIF(Contains([DescriptionColumn], "Sales"), "Sales","Unknown")

... or ...

IF (Contains([DescriptionColumn],"Payroll") THEN
 "Payroll"
ELSEIF (Contains([DescriptionColumn], "Sales") THEN
 "Sales"
ELSE
 "Unknown"
ENDIF

Those are a few thoughts, just regular formula stuff, not RegEx, but a RegEx could probably work too.

Hope that helps!

[Troy]

I personally avoid using IF statements and instead use mapping files (either XLSX or Text Input) for this type of situation.  Attached is an example.  The main reason I like this is I think it is easier to give yourself a warning when a new value appears that you haven't accounted for which happens all to often.

Other things, check for spaces (leading and trailing) as well as correct case in the Description Column. 

If you want to make it case insensitive add a formula before L & R inputs of Join Tool with JOINFIELD = LowerCase(Trim([Description Column])) and use that to JOIN.

Hope that helps.  Best.