Combine three fields into one field

[bhaveshp90]

Hello, 

I have a table with Cost_Area  field which has values as:

I am trying to combine multiple fields ('CONT HDWR', 'CONT RESALE', 'CONTROLS ENG') in load script as one field  (named "Controls Eng"). So, my resultant column "Controls Eng" would be a list having values like 'CONT HDWR', 'CONT RESALE', 'CONTROLS ENG' and not the other fields.  

How can I do this? 

attach is the file for reference

[rubenmarin]

Hi, for only that 3 values maybe just an If:
If(Match([Cost Area], 'CONT HDWR', 'CONT RESALE', 'CONTROLS ENG'), [Cost Area]) as [Controls Eng]

[bhaveshp90]

@rubenmarin  I tried the Match() but my goal is to create a separate column "Area"

where Controls Eng = 'CONT HDWR', 'CONT RESALE', 'CONTROLS ENG' values 

Mechanical Eng = 'MECH HDWR', 'MECH INSTALL', 'MECH RESALE' values 

[passionate]

Use Appymap by creating a mapping table of whatever you require and create a separate column in data model for this.

[rubenmarin · Accepted solution]

It can be done with a nested if:

If(Match([Cost Area], 'CONT HDWR', 'CONT RESALE', 'CONTROLS ENG'), 'Controls Eng',
If(Match([Cost Area], 'MECH HDWR', 'MECH INSTALL', 'MECH RESALE'), 'Mechanical Eng')
) as [Area]

Using variables:

SET vControlsEng = 'CONT HDWR', 'CONT RESALE', 'CONTROLS ENG';
SET vMechanicalEng = 'MECH HDWR', 'MECH INSTALL', 'MECH RESALE';

LOAD ...
If(Match([Cost Area], $(vControlsEng)), 'Controls Eng',
  If(Match([Cost Area], $(vMechanicalEng)), 'Mechanical Eng'
) ) as [Area]
...

Or using applymap (recommended):

mapArea:
LOAD * inline [
CostArea, Area
CONT HDWR, Controls Eng
CONT RESALE, Controls Eng
CONTROLS ENG, Controls Eng
MECH HDWR, Mechanical Eng
ECH INSTALL, Mechanical Eng
MECH RESALE, Mechanical Eng
];

LOAD ...
Applymap('mapArea', [Cost Area], null()) as Area
...