Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

CREATING A NEW COLUMN INCLUDING INFORMATION FROM AN EXISTING COLUMN

Lauren_Holmes
8 - Asteroid

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 :).

4 REPLIES 4
Luke_C
17 - Castor

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
11 - Bolide

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
8 - Asteroid

@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
8 - Asteroid

@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