- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Combine three fields into one field
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If(Match([Cost Area], 'CONT HDWR', 'CONT RESALE', 'CONTROLS ENG'), [Cost Area]) as [Controls Eng]
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Use Appymap by creating a mapping table of whatever you require and create a separate column in data model for this.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ...