Defining Complex Mappings

Temporary Variables

Sometimes the desired mapping cannot be performed by a single mapping type. If this is the case, it can often be performed by breaking down the mapping transformation into several steps, with each step using a different mapping type.

To store the partial results for use in a subsequent mapping, you can use temporary variables.

To define temporary variables, use the Variables... button on the Field Mappings tab. Each variable must have a unique identifier and must have a data type.

Once mapped, temporary variables can be used in assignment mapping expressions, lookup queries and as the source for value match mappings.

Example Use of Temporary Variables to Handle Complex Mappings

Example 1

Use Case: The target field owner holds an id reference to a user. The source table has two user fields; assignee and requestor. Again, these hold the id of the (source) user. The owner field is mandatory.

It is required that the target owner field should hold the source assignee if there is one. If there is not, the owner should hold the requestor.

We can't use a single lookup mapping here, since there are two possible source values. We can't use an assignment mapping alone either, since we are dealing with id values that are different on source and target.

This is one possible way of implementing this requirement using multiple steps and storing the result of each step in a temporary variable:

1. create a temporary variable ASSIGNEE_USER_ID and map this using a reference mapping. This will get the ID of the target user that correlates with the source assignee user. It will be set to NULL if there is no assignee user.
2. create a second temporary variable REQUESTOR_USER_ID and map this using a reference mapping. This will get the ID of the target user that correlates with the source requester user.
3. map the owner field using an assignment expression, using the FIRST_WITH_VALUE function. This takes 2 parameters, the first being the temporary variable ASSIGNEE_USER_ID and the second REQUESTOR_USER_ID

With these three mappings working together, we can fulfil the mapping requirement.

Example 2

Use Case: The source and target record hold a category value. rather than the name of the category, both store the ID of a category. However, the correlation between categories is not simple, with many categories having different names on the source and target.

We can't use a reference mapping here because there is no correlation field between categories. We could use a value match mapping but would have to match the ID's rather than the category names which might be error prone and difficult to see what is going on in the mapping. It might also not be easy to identify the ID's that should be used.

This is one other way the mapping might be performed:

1. Create a temporary variable SOURCE_CAT_NAME. This should be mapped using a lookup mapping to obtain the name if the category referenced on the source (by ID)
2. Create a temporary Variable TARGET_CAT_NAME. This should use a value match mapping to map the source category name (given by the variable SOURCE_CAT_NAME) to the appropriate target name.
3. Map the target category field using another lookup mapping. This will perform a lookup on the target category table to get the ID of the category given by TARGET_CAT_NAME

Again, by using a series of mapping steps, we have met the requirement.