Creating and applying mapping tables

This guide explains how to create a mapping table and how to apply it to a datastream.

Introduction

A mapping table is a table that links source values to target values. Mapping tables are used in transformations. For example, you can use a mapping table to map country codes (e.g. DE) to country names (e.g. Germany) to include this new data in your data extract.

Mapping tables contain the following two columns:

  • MATCH - Populate this column with the source values contained in a data extract (for example, DE) or a regular expression you want to match (for example, /.*DE.*/).

  • VALUE - Populate this column with target values to map on to the source values in the MATCH column (for example, Germany).

Additionally, you can define a list of target values to populate the VALUE column of a mapping table. This list is called a value table. For more information on how to create a value table and apply it to a mapping table, see Creating value tables.

This guide outlines how to create a mapping table and apply it to a datastream using the map custom script. However, this option is considered an advanced approach as it allows for a lot of customization. For an easier way to apply mapping tables to your datastream, consider using the Match & Map or Unify Location Data transformation. For more information on these transformations, see the following:

For more information on configuring a mapping table, importing a mapping table and using value tables, see Advanced mapping table settings.

Creating a mapping table

Prerequisites

Before you complete the procedure in this guide, perform all of the following actions:

  • Make sure that the data collected from the datastream contains source values used in the mapping table.

Procedure

To create a mapping table, follow these steps:

  1. Go to the Transformations page.

  2. Under the page heading, click Mapping tables.

  3. In the top right corner, click + Create mapping table.

  4. In Name, provide a name for the mapping table.

  5. (Optional) To use target values from a value table in the value column, select a value table in Default value table. For more information, see Advanced mapping table settings.

  6. In the Mapping Entries section, click Add another Mapping Entry.

  7. In the MATCH column, enter the source value or a regular expression. For example, enter DE to match entries that contain this exact value, or /.*DE.*/ to match entries that contain DE within a longer sequence (for example, code-DE-01).

  8. In the VALUE column, enter the target value. For example, enter Germany.

  9. (Optional) To link a target value from a value table, click Link. For more information on linking values from a value table, see Populating a mapping table using a value table.

  10. (Optional) To add another entry to the mapping table, repeat steps 6 to 9.

  11. Click Save.

As a result, the mapping table is created and can be applied to a datastream using a custom script.

Creating a mapping transformation script

Prerequisites

Before you complete the procedure in this guide, perform all of the following actions:

  • Make sure that the data collected from the datastream contains source values included in the mapping table. Find the name of the data extract column that contains the source values. For more information, see Sourcefield below.

Procedure

To create a mapping transformation script that applies a mapping table to a datastream, follow these steps:

  1. Go to the Transformations page.

  1. In the top right corner, click + Create transformation.

  2. Click Custom Script.

  3. To assign the transformation to individual datastreams of your choice, follow these steps:

    1. In the Assign to section, select Individual datastreams.

    2. In the drop-down menu, select the datastreams to assign the transformation to them.

  4. To assign the transformation to all existing and new datastreams that belong to Datastream Types of your choice, follow these steps:

    1. In the Assign to section, select Datastreams Types.

    2. In the drop-down menu, select the Datastream Types to assign the transformation to all datastreams that belong to these types.

    3. (Optional) In the Do not assign to these individual datastreams drop-down menu, select the individual datastreams that belong to the Datastream Types that you have selected, but you do not want to assign the transformation to them.

  1. Click Next.

  1. In Select Instruction, enter and select map.

  2. Fill in the following fields:

    Sourcefield

    Enter the name of the column in the data extract that contains the source values. Use regular expressions in the MATCH column to expand the search criteria.

    Fieldname

    Enter the name of the new column to be added to the data extract. This new column is populated with the target values.

    Mapping

    Enter the name of the mapping table to use in the map transformation.

    Missing

    (Optional) Determine the course of action if a source value in the data extract is not present in the MATCH column of the mapping table. Select one of the following options:

    • (Default) Select ignore to ignore the missing mapping entry and continue with the transformation process.

    • Select create to populate the new column of the data extract with a predefined value and continue with the transformation. Enter a value to populate the new column with in the Alternative field. For example, if a data extract contains a value not present in the mapping table and the Alternative field contains the text 'Match not found', then the data in the new column is populated with the text Match not found.

    • Select stop to stop the transformation process. A new column is not created in the data extract and an error message is shown in the datastream overview.

    • Select create,stop to populate the new column with the value from the data extract that is missing from the mapping table and stop the transformation process. This highlights where the missing mapping entry is found.

    Errors

    (Optional) Select what happens if an error occurs during the transformation. Select one of the following options:

    • (Default) Select raise to display an error message in the datastream overview if an error occurs. Customize the text displayed in the error message using the Comment field.

    • Select ignore to ignore any errors that occur and continue processing the transformation script.

    Notify

    (Optional) Select this checkbox to send notifications to users subscribed to the Datastream and transformation Issues and Mapping topics. For more information on setting up notifications, see Managing user notifications. Customize the text in the notifications using the Comment field.

    Flush Table

    (Optional) Select this checkbox to remove all values from a mapping table. Only select Flush Table if a mapping table is to be populated with values from a data extract. For more information on how to automatically populate a mapping table, see Automatically populate a mapping table.

    If

    (Optional) Enter a python expression. The mapping table is applied to the data extract only if the condition of the expression is true.

    Comment

    (Optional) Enter text to include in the notifications or error messages. The text must be entered as a Python expression. For example, to include the text Cannot find value: [name of value] in Data Extract, enter the following Python expression:

    'Cannot find value:%s in Data Extract' % {sourcefield_column_name}

    In this example, replace sourcefield_column_name with the name of the column in the data extract that contains the source values. You must contain the name of the column within braces {}.

    Alternative

    (Optional) Enter a value to populate the new column of data extract if a value in the Sourcefield column cannot be matched to a value in the mapping table. The text must be entered as a Python expression. For example, to populate the column with a value of Invalid, enter the python expression 'Invalid'.

    Subtable

    (Optional) In Subtable, create a subtable to duplicate the data extract. Enter a name for the sub-table as a Python expression, for example, 'Subtable Name'.

  3. Click Create +.

As a result, the mapping transformation script is created and can be applied to a datastream.

Applying a mapping transformation to a datastream

Prerequisites

Before you complete the procedure in this guide, perform all of the following actions:

Applying the transformation to a datastream

To apply the transformation to a datastream, follow these steps:

  1. Go to the Datastreams page.

  2. Click the datastream to which you want to apply the transformation.

  1. In the Transformations section, click + Add transformation.

  2. Click Assign Existing transformation.

  3. To the right of the transformation you want to apply to the datastream, click + Assign

  1. In the top right corner of the page, click Close to close the Assign transformations window.

As a result, the mapping table is applied to the datastream. Click Fetch now to collect data in the datastream with the applied transformation script and view the mapped data in the new column of the data extract.

Automatically populating a mapping table

Mapping tables can be automatically populated with values taken from a data extract. This feature is used to update mapping tables with the latest mapping data to be applied during transformations.

To automatically populate a mapping table, create a transformation script using the map instruction.

For more information on how to create this transformation script, see Automatically populate a mapping table.