To define the relationships between values across your systems of record, you can set up a mapping group with a series of rules.
Requirements
Data Prep is controlled entirely at the org level and does not recognize individual workspaces or their permissions.
This means:
- Data Prep is shared among all authorized users in your org.
- Any user with access to Chain Builder also has access to Data Prep.
- All users who can create or edit chains will have the ability to manage pipelines in Data Prep.
- A single Data Prep pipeline can be used across multiple chains and workspaces within an organization.
Add a mapping rule
To add a new rule to a mapping group:
- From Wdata Chains, click Data Prep.
- From Mapping groups , open the mapping group.
- On the Rules select how to map values:
- Exact, to map based on an exact match
- Like, to map based on a simple pattern
- Regex, to map based on a regular expression
tab, click Add, and - In From, enter the criteria of source values from the primary column to transform.
- In To, enter how to transform values that match the From criteria.
Tip: To enable flexibility, you can select variables create runtime variables.
for the From or To values. To prompt for these values when the pipeline runs, - To apply any conditional mapping, in Conditions, specify when to apply the rule.
- To help identify the rule, enter its name and description.
Tip: In the mapping group, you can search for rules by name.
- If the group includes multiple rules, in Position, enter the order in which the rule should apply.
Note: Use Position to address overlapping criteria. For example, say you have two rules; one maps a source value of
100011
toShort term investments
, another maps any source value that begins with1000
toCash
. Since100011
meets the criteria of both rules, to prioritize the mapping toShort term investments
, enter1
for its Position. - Click Save, verify the rules are set up correctly, and click Save.
Apply conditional mapping to a rule
To consider multiple columns when mapping values in tabular data, apply conditions to the rule:
- In the mapping group, on the Columns tab, define the columns to consider in the condition.
- On the Rules tab, click Add, and select the type of rule to add.
- In From and To, enter the criteria of the values from the primary column to transform, and how to transform those values.
- In Conditions, click Edit
, and add the additional conditions to consider to apply the mapping. - Click Submit and Save.
For example, say your salary and expense (S&E) reporting varies based on the employee's function; the S&E of employees involved in the production of goods is recognized as part of the cost of goods, while non-production employees are recognized as an operating expense. You can add rules to consider both the employee's account and department to map values appropriately:
- Define GL account as the primary column, and add Department as an additional column.
- Add two Exact rules to map the source value of
Salaries
to eitherCost of Goods
orOperating Expense
, respectively. - Add the conditions for each rule:
- For the rule mapping to
Cost of Goods
, add a conditional rule ofDepartment = Production
. - For the rule mapping to
Operating Expense
, add a conditional rule ofDepartment != Production
.
- For the rule mapping to
With these rules, the GL account value of Salaries
maps to either Cost of Goods
or Operating Expense
depending on whether the Department value is Production
.
Set rule properties
From a mapping group's Properties
tab, you can set the properties of its rules:- To consider the casing of the rules' values, select Case sensitive.
- To remove leading or trailing blank space from the rules' values, select Trim whitespace.
- To apply the rules only during a specific time period, enter the group's start and end dates.
Tip: To begin or stop the use of a group in pipelines as of a specific date, enter only a Start date or End date.
Edit mapping rules
From a mapping group's Rules
tab, you can edit the mapping rules at any time.Note: If you edit a rule's Type, update its From and To values as necessary to map correctly.
To edit a large number of mapping rules:
- On the Rules tab, click Download to generate the existing rules as a comma-separated values (CSV) file.
- In a text editor, open the CSV, and edit the rules as necessary.
- On the Rules tab, click Upload, and import the updated CSV.
Download mapping rules
To create an offline backup of a mapping group's rules from the Rules
tab, click Download to generate a CSV file of the rules.Note: To prevent formatting in the CSV file, open it in a text editor—such as Notepad++—instead of Microsoft Excel®, or select Get data in Excel to import the CSV into a workbook.
Upload mapping rules
You can import rules to a mapping group as a comma-separated values (CSV) file, such as to seed the group or make mass updates.
Note: When you upload mapping rules, they replace the group's existing rules.
The import file must:
- Be a valid CSV
- Have a header row of
type,source,target,conditions,name,description
- Have each field present on every row
Note: A row can include null or blank values.
- Have no blank rows
Tip: For a template CSV, click Download on the group's Rules tab, and update the file as necessary.
To import the rules as a CSV:
- On the Rules
tab, click Upload. - Drag or browse to the CSV file, and click Submit.
Delete mapping rules
If rules no longer apply to the values transformed by the mapping group, remove them from the group.
Tip: To create a backup of the rules before you delete them, click Download to generate a CSV file.
- On the Rules tab, select the rules to delete.
- Click Remove and Save.