ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

CREATING A NEW COLUMN INCLUDING INFORMATION FROM AN EXISTING COLUMN

Lauren_Holmes
6 - Meteoroid

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
12 - Quasar

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. 

 

 

Luke_C_0-1617894353612.png

 

MattBSlalom
8 - Asteroid

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

KeywordCode
REPAIRREP
CALIBRATIONCAL
SERVICEPM
INSTALLATAIONINS

 

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.

 

MattBSlalom_0-1617894292434.png

 

MattBSlalom_1-1617894339963.png

 

 

 

Lauren_Holmes
6 - Meteoroid

@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
6 - Meteoroid

@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. 

Labels