Automatically populate a mapping table

This guide explains how to create a transformation script that will automatically populate a mapping table with data from a data extract.

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).

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

Use a transformation script to update a mapping table with the latest data from a data extract. Once updated, all datastreams using this mapping table will have the latest values during the transformation process.

Prerequisites

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

  • Find the name of the columns in the data extract that contain the data used to populate the mapping table.

Procedure

To create a transformation script that automatically populates a mapping table with data from 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 to populate the MATCH column of the mapping table.

    Fieldname

    Enter the value DeleteMe.

    This is a required field, but it is not used in this use case. This column will be removed using a separate instruction described in the following steps of this guide.

    Mapping

    Enter the name of the mapping table to be populated.

    Missing

    Select the create option to add missing values to the MATCH column of the mapping table.

    Errors

    Select the ignore option.

    Flush Table

    (Optional) Select this checkbox to delete all previous entries in the mapping table each time the transformation script is run. This step ensures the mapping table is populated with only the latest data.

    If you want to only add the new values to the mapping table and keep the existing entries, leave this checkbox empty.

    Alternative

    Enter the name of the column in the data extract containing the target values that should be used to populate the VALUE column of the mapping table. This field has to be configured as a Python expression.

    For example, to populate the VALUE column with a value of the column date, enter the expression {date}. Alternatively, to populate the column with a value Invalid, enter the expression 'Invalid'.

  3. Below the map instruction, click Add Instruction.

  4. In Select Instruction, enter and select cutout.

  5. In Fields, click Add.

  6. In the drop-down field, select String and enter DeleteMe into the field next to it.

  1. Click Next.

  1. (Optional) In Transformation name, rename the transformation.

  2. Click Create +.

As a result, the transformation script is created and can be applied to the relevant datastream to update the mapping table. For more information on how to apply a transformation script, see Applying a mapping transformation to a datastream.