Combine the multiple values from the field (as separate column)

[bhaveshp90]

Hello, 

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

I'm trying to create a separate column "Area" as shown in the image below;

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

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

I came to know that using Appymap we can create a mapping table of required fields and create a separate column, if this approach is the right way How can I do this? 

attach is the file for reference

thanks

[Shubham_Deshmukh]

Hey Bhavesh,
If you are extracting this data from database, then just do this using CASE WHEN, it would be the better way.

Regards

[dplr-rn · Accepted solution]

Apply map is the way to go
load inline your mapping or create a mapping excel which contain key value pairs like below
CostAreaMap:
mapping load * inline
[Key, Value
CONT HDWR, Controls Eng
CONT RESALE, Controls Eng
...
]
i see you have done a big nested if statement for [Cost Area]. put all that info in the map
then in your load replace you if statements by
ApplyMap('CostAreaMap', [Cost Area], [Cost Area]) as [Cost Area]

syntax and exapmples here
https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/MappingFun...

[dplr-rn]

Mark as answered if it helped. both here and in the original thread