topic Re: Combine the multiple values from the field (as separate column) in QlikView

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

bhaveshp90 — Sat, 16 Nov 2024 04:04:29 GMT

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

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

Shubham_Deshmukh — Fri, 22 Mar 2019 13:53:07 GMT

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

Regards

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

dplr-rn — Fri, 22 Mar 2019 13:53:55 GMT

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/MappingFunctions/ApplyMap.htm

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

dplr-rn — Fri, 22 Mar 2019 13:55:06 GMT

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