CREATING A NEW COLUMN INCLUDING INFORMATION FROM AN EXISTING COLUMN

Question

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 P78REQUEST ID: 3647156 CALIBRATION X4REQUEST ID: 157964 REPAIR P1 lid switchREQUEST 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 BREPCALREPPMINS

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

Thank you in advance :) .

Luke_C · Accepted Answer

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 Answer

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

KeywordCodeREPAIRREPCALIBRATIONCALSERVICEPMINSTALLATAIONINS

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.