There are three mapping types that are available for use in Mapping Groups.

📓 A single Mapping Group can contain any combination of the available mapping types. Additional criteria can also be assigned to any mapping rule type to create a Conditional Map.

Exact Mapping Type

An Exact mapping type is the most simplistic mapping type where the relationship between the systems being mapped is explicitly specified. Any user, regardless of technical acumen, is able to interpret the impact that the mapping rule will have on the data. This simplicity is its chief benefit.

For example, the product code of 100-10 is mapped to the reporting system code of Regular Cola. This is an explicit one-to-one (1:1) relationship.

The downside of the Exact mapping type is that Mapping Groups need to be updated whenever a new code is added to the system from which the data extract is generated and to which the Mapping Group will be applied.

Example Exact Mapping Rule

Like Mapping Type

A Like mapping type is a powerful way to account for simple patterns in the data models of the different systems being integrated. They can be used to support many-to-one (n:1) and many-to-many (n:n) mapping relationships, as well as concepts such as prefixing, suffixing, and trimming. The Like mapping type utilizes two types of wildcards: question marks and asterisks.

The Question Mark ?

A question mark (?) is a wildcard that denotes a single character. The question mark is useful when the pattern needs to account for values that need to be a certain number of characters or when the pattern to be matched is in the middle of the value that needs to be transformed.

Example Single Character Wildcard Like Mapping Rules

From

To

Explanation

Example

??130

IT

Many-to-one relationship requiring the source value to be five digits in length and end with the value 130

Unmapped Value:
23130

Mapped Value:

IT

???

???

Many-to-many relationship that maps the source value to itself but only when the source value is three characters

Unmapped Value:
630

Mapped Value:

630

??????

AC_??????

Many-to-many relationship that maps a six character field to itself and adds a prefix of AC_

Unmapped Value:
601010

Mapped Value:

AC_601010

CC_????

????

Many-to-many relationship that maps a field value that begins with CC_ and is seven characters to the last 4 characters of the field

Unmapped Value:
CC_6425

Mapped Value:

6425

100-??-4000

Revenue

Many-to-one relationship that maps a field that is 11 characters, begins with 100-, and ends with -4000

Unmapped Value:
100-80-4000

Mapped Value:

Revenue

The Asterisk *

An asterisk (*) is a wildcard that denotes multiple characters. The Like mapping type supports common concepts such as begins with or ends with. The use of the asterisk supports mapping system values that are variable in length.

Example Multiple Character Wildcard Like Mapping Rules

From

To

Explanation

Example

*065

Finance

Many-to-one relationship requiring the source value end with the value 065

Unmapped Values:
10065, 9065

Mapped Value:

Finance

*

*

Many-to-many relationship that maps the source value to itself regardless of the number of characters. This is often called pass-through mapping.

Unmapped Values:
500010, 1400, Salaries

Mapped Values:

500010, 1400, Salaries

*

CC_*

Many-to-many relationship that maps the source value to itself but appends a prefix of CC_ to the mapped value.

Unmapped Values:
92230, 81010, Quality

Mapped Values:

CC_92230, CC_81010, CC_Quality

BU_1*

1*

Many-to-many relationship that maps the source value to itself when the source value begins with BU_1. The BU_ prefix is removed from the mapped result.

Unmapped Values:
BU_1200, BU_1000, BU_2300

Mapped Values:

1200, 1000, <BU_2300 is not mapped by this rule because the source value does not match the pattern>

The benefit of the Like mapping type is its ability to reduce the mapping rule maintenance burden. Since wildcards can be used to account for patterns, new values added to the various systems being integrated that adhere to the pattern are automatically mapped by the pattern based Like mapping rules. This also reduces the number of mapping rules that need to be created both initially and over time.

A downside of the Like mapping type is that this requires that the data model of the systems being integrated need to employ some form of standards for how values are defined and maintained.

For example, if the cost center range of 060-080 is reserved for Finance cost centers, then an IT cost center created as 92070 would violate that standard.

Data Prep is flexible and can account for this inconsistency by simply adding an Exact mapping type exception and adjusting the position of the Exact map to be before the Like map.

Regex Mapping Type

A Regular Expression or Regex mapping type is a very powerful way to account for more complex patterns in the data models of the different systems being integrated that cannot be addressed using a Like mapping rule.

Regex mapping rules utilize the concept of segments. A segment is identified by open and closed parentheses. Multiple Regex expressions can be used to create the mapping criteria and any of the segments created by the regex expression can be utilized to create the mapped value.

The benefit of the Regex mapping type is its ability to perform advanced pattern matching. This can significantly reduce the volume of maps that would be required with Exact and Like mapping types.

The downside of the Regex mapping type is its complexity.

📓 An effective Mapping Group will often employ a combination of the available mapping types.

Example Regex Mapping Rules

From

To

Explanation

Example

([4-7])(.*)

$2

Many-to-many relationship that maps a source value that begins with 4, 5, 6, or 7 to everything after the initial digit

Unmapped Values:
5-100, 93065, 7625

Mapped Values:

-100, <Not mapped>, 625


📚 Related Topics from this Section:

Conditional Mapping

Mapping Rule Processing Order

Common Regular Expressions for CSV


Did this answer your question?