When you add rules for a mapping group, you can define the relationships between values from your systems of record. To map values between systems of record, you can define relationships based on an exact match, a simple pattern, or regular expression (Regex).
Tip: Effective mapping groups often include multiple rules of different types. To apply additional criteria to a rule, you can also set up conditional mapping.
Exact
Exact rules define an explicit one-to-one relationship between values. For example, to map a source value of 100-10 directly to a product code of Regular cola, apply an Exact rule.
Note: With an Exact rule, any new values to the data transformed by the pipeline require an update to the mapping group with a rule for the new value.
Like
Like rules define relationships based on simple patterns or standardized values. For example, you can use a Like rule to define many-to-one (n:1) or many-to-many (n:n) relationships based prefixes, suffixes, or trimming.
To help match patterns for a Like rule, you can use a question mark (?) or asterisk (*) as a wildcard for single or multiple characters, respectively.
A ? wildcard replaces a single character, such as when the pattern to match requires:
- Values of a certain number of characters
- Specific characters in the middle of values
For example:
| From | To | Explanation | Result |
|---|---|---|---|
??130 |
IT |
A many-to-one relationship, where any source value of five digits that end with 130 maps to the value IT |
A source value of 23130 maps to IT |
??? |
??? |
A many-to-many relationship, where any source value of only three digits maps to itself | A source value of 630 maps to 630 |
?????? |
AC_?????? |
A many-to-many relationship, where any source value of six characters maps to itself with a prefix of AC_ |
A source value of 601010 maps to AC_601010 |
CC_???? |
???? |
A many-to-many relationship, where any source value of seven characters that start with CC_ maps to its last four characters |
A source value of CC_6425 maps to 6425 |
100-??-4000 |
Revenue |
A many-to-one relationship, where any source value of 11 characters that begin with 100- and end with -4000 maps to the value Revenue |
A source value of 100-80-4000 maps to Revenue |
An * wildcard replaces multiple characters, such as when the pattern to match requires specific characters at the start or end of values of variable length. For example:
| From | To | Explanation | Result |
|---|---|---|---|
*065 |
Finance |
A many-to-one relationship, where any source value that ends with 065 maps to the value Finance |
A source value of 10065 or 9065 maps to Finance |
* |
* |
A many-to-many relationship, where any source value maps to itself regardless of characters; this is known as pass-through mapping | A source value of 500010, 1400, or Salaries maps to itself—500010, 1400, or Salaries, respectively |
* |
CC_* |
A many-to-many relationship, where any source value maps to itsself with a prefix of CC_ |
A source value of 92230, 81010, or Quality maps to CC_92230, CC_81010, or CC_Quality, respectively |
BU_1* |
1* |
A many-to-many relationship, where any source value that starts with BU_1 maps to itself without the BU_ prefix |
A source value of BU_1200 or BU_1000 maps to 1200 or 1000, respectively.
Note: This rule would not map a source value of
|
Regex
Regular expression—or Regex—rules define relationships based on more complex patterns that can't be mapped with a Like rule. To identify patterns, regular expressions use segments, indicated by open and closed parentheses (()). You can can use multiple regular expressions to create mapping criteria, and any segment created by the regex can create the mapped value.
For example:
| From | To | Explanation | Result |
|---|---|---|---|
([4-7])(.*) |
$2 |
A many-to-many relationship, where any source value that begins 4, 5, 6, or 7 maps to its value after the initial digit |
A source value of 5-100 or 7625 maps to -100 or 625, respectively
Note: This rule would not map a source value of
|