convert code to actual text

[vern]

what is the best formula to use to convert codes to actual text; example;  A = Excellent, B = Average, F = Fail, etc.? within Alteryx

[Jean-Balteryx]

Hi @vern ,

I built the attached solution that does what you want using either an IF statement or a Switch function.

Ask me if you need more details.

Cheers,

Jean-Baptiste

[jdunkerley79]

Two options either using a switch statement:

Switch([Code],[Code],
       "A","Excellent",
       "B","Average",
       "F","Fail")

Alternatively, look at using a Find and Replace tool which is a good fit for this

[Qiu]

@vern 
Maybe the Find and Replace is suitable for your requirement.

[vern]

i'm very new to Alteryx and these formulas...

the code, code

i'm assuming the first code is the field in the table ("A") and the 2nd code is the verbiage ("Excellent"); can the 2nd code be a new field being created or is it replacing the first field? i hope this makes sense.

[vern]

so, this makes more sense to me than the switch; however, the fields are actually being changed to the new values, correct?

[jdunkerley79]

The [Code],[Code] in the switch statement says use the Code field's value (first argument) and if no following value matches return [Code] (the second value).

Each pair of argument after that is the value to match and the value to use.

[Jean-Balteryx]

@vern ,

The solution using IF statements I sent you may be more understandable.

Cheers,

Jean-Baptiste

[vern]

IF [EEO_CLASS] = "AN"
THEN "American Indian/Alaska Native"
ELSEIF [EEO_CLASS] = "AS"
THEN "Asian"
ELSEIF [EEO_CLASS] = "B"
THEN "Black"
ELSEIF [EEO_CLASS] = "DI"
THEN "Declined to Identify"
ELSEIF [EEO_CLASS] = "DS"
THEN "Declined to Identify"
ELSEIF [EEO_CLASS] = "HI"
THEN "Hispanic or Latino"
ELSEIF [EEO_CLASS] = "NHPI"
THEN "Nat Hawaii/Pacific Islander"
ELSEIF [EEO_CLASS] = "TWO+"
THEN "Two or More Races"
ELSEIF [EEO_CLASS] = "WH"
THEN "White"
ELSE "Ethnicity Unknown"
ENDIF

i'm now getting the error "The field "" is not contained in the record. (Expression #2)

[Jean-Balteryx]

It's because you didn't select a field or created a new one in the formula tool.