topic Re: Combine three fields into one field in QlikView

Combine three fields into one field

bhaveshp90 — Sat, 16 Nov 2024 04:05:13 GMT

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

Re: Combine three fields into one field

rubenmarin — Thu, 21 Mar 2019 18:24:36 GMT

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]

Re: Combine three fields into one field

bhaveshp90 — Thu, 21 Mar 2019 18:37:04 GMT

@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

Re: Combine three fields into one field

passionate — Thu, 21 Mar 2019 19:19:10 GMT

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

Re: Combine three fields into one field

rubenmarin — Mon, 25 Mar 2019 12:22:00 GMT

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