Applying Data Mapping Patterns

Altova MapForce includes powerful mapping components that correspond to design patterns for data transformation requirements. Analyzing a data mapping challenge up front and following a few straightforward guidelines can uncover data mapping patterns that help simplify creation of the mapping design and lead to an optimal solution. The MapForce Examples project provides sample mapping files and data sets that illustrate many common data mapping patterns. Reviewing these examples and executing them with the MapForce Built-in Execution Engine is another good way to help select the best pattern for your own project.

Exact vs. Conditional Matching

First, determine whether your inputs and outputs have an exact one to one relationship or require analysis of a more complex condition such as from-to ranges or an if-else statement.

Exact matching lets you express your requirement with a simple two-column table and the algorithm “look for an exact match of the given input value in the left column and use the value in that line from the right column.” Note that the items in the left column must all be unique, while there may be duplicate values in the right column.

Examples of exact matching include mapping month names to numbers, or converting ISBNs to book titles.

Exact Matching Options

If the number of entries in the table is constant and rather small (say, up to 20, or in exceptional cases maybe up to 200), use a value-map component. Typical examples are converting month names to numbers, or converting codes to other codes (e.g. “F” to “XX” and “M” to “XY”).

You can even rename the input and result column names of the value-map component. In the value-map properties dialog just double-click on the name “input” or “result” (or click the pen icon) and start typing. This makes it easy to identify the purpose of multiple value-map components in the mapping.

The MapArticle.mfd example demonstrates use of the value-map component.

We are sometimes asked why there is no setting for passing an unknown input value unchanged to the output. The answer is that would not work if the data types in the input and output side are different, such as mapping from an integer to a string as shown above.

The solution here is to leave the Otherwise option unchecked (value-map of an unknown input will then return an empty sequence), and use the node function substitute-missing from the core function library at the output to substitute the empty sequence with the original input value.

Larger Look-Up Tables

For larger numbers of entries or lookup tables that change frequently, a database lookup is the best solution. This means that the actual values of the lookup table are not part of the mapping but stored in a database. Add a database component and SQL-WHERE component to the mapping to implement this pattern. The DB_PhoneList.mfd example illustrates this strategy.

If you cannot use a database and the number of entries is not very large, you can also use any other component instead, e.g. XML or CSV, and a filter component, as seen twice in the example CompletePO.mfd. However, this solution can be slower at runtime than using a database with WHERE.

For some requirements, you may even want to call a Web service to look up values. The examples BookISBNConvertWS.mfd and CurrencyConverter.mfd illustrate use of a Web service for look up.

Conditional Matching

If you need more complex conditions than exact matching, such as checking from-to ranges of input values, use the if-else component, as seen in the example ClassifyTemperatures.mfd.

The red plus signs in the if-else component let you add more tests, each based on a different logical function. The otherwise input lets you define the result in a case where none of the logical tests is true.

Complex Mappings and Component Reuse

If your data transformation requirement involves multiple inputs and outputs, you can easily include multiple value-maps and if-else components in a single mapping.

You can duplicate any of the components described above using copy-and-paste within a single design or across mapping files. You can also reuse any of the solutions described here by wrapping them in a User-Defined Function. You can save your own functions in a user function library to apply later in other projects or share with colleagues.

Find out for yourself how easy it is to apply MapForce data mapping patterns in your own data transformation projects! Download a free 30-day trial of MapForce.