CREATING A NEW COLUMN INCLUDING INFORMATION FROM AN EXISTING COLUMN

[Lauren_Holmes]

Hello, I was hoping someone may be able to help me. 

I am trying to create a new column using existing data from a current column, but the new data within the new column needs to be in specific code. EG:

EXISTING COLUMN

COLUMN A

REQUEST ID: 1326584 REPAIR P78

REQUEST ID: 3647156 CALIBRATION X4

REQUEST ID: 157964 REPAIR P1 lid switch

REQUEST ID: 7846216 SERVICE

REQUEST ID: 452136 INSTALLATION

I would like the workflow to read for example - IF COLUMN A READS/INCLUDES "REPAIR" OR "CAL" OR "SERVICE" THEN NEW COLUM SHOULD READ "REP", "CAL", "PM".

The new column would ideally read: 

COLUMN B

REP

CAL

REP

PM

INS

Please could someone advise how I would go about doing this? 

Thank you in advance :).

[Luke_C · Accepted solution]

Hi @Lauren_Holmes 

Here's two approaches:

• Use an if statement to check for each key word (i.e. IF Column A contains 'Repair' then 'REP' else...)
• Use a find and replace to compare against a 2nd table of keywords

Depending on your data the 2nd option may be more scalable/require less maintenance if new keywords are ever added. 

[MattBSlalom · Accepted solution]

First you'll need a dataset of all the possible translations you want to perform like:

Keyword	Code
REPAIR	REP
CALIBRATION	CAL
SERVICE	PM
INSTALLATAION	INS

Then use an Append Fields tool to attach this list to all your incoming rows.  Next add a Filter tool with an Expression like:

Contains([COLUMN A], [Keyword])

The T anchor of the Filter will have your translated rows.

[Lauren_Holmes]

@Luke_C This is great thank you so much! I think the table option is best in case any other variables come in!

[Lauren_Holmes]

@MattBSlalom This is exactly what i need, thank you so much, I have never used the Append Fields tool before so this is great to try out.