Create or Edit Columns

This guide explains how to use the Create or Edit Columns transformation to create or edit columns in data extracts.

Introduction

The Create or Edit Columns transformation allows you to add new columns to data extracts or edit existing columns. Use this transformation to create a new column and then populate this new column with either a static value, values from existing columns, or a combination of new and existing values.

You can apply rules to this transformation to specify instructions for specific conditions. For more information on creating and applying rules for the Create or Edit Columns transformation, see Adding rules to the Create or Edit Columns transformation.

The Create or Edit Columns transformation uses the following custom script instructions in the background:

• addfield

• addfieldx

• convertx

The Create or Edit Columns transformation uses a combination of these instructions to add new columns to the data extract and populate them with values.

This video guide explains how to create and configure a Create or Edit Columns transformation.

Prerequisites

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

• Create a datastream.

• Fetch data for the datastream.

• Apply the correct Data Mapping to your datastream.

Configuring the Create or Edit Columns transformation

This guide explains how to configure the Create or Edit Columns transformation. Before completing this step, start creating a transformation by following the instructions in Using standard transformations. Choose how you want to use the Create or Edit Columns transformation:

• Creating a new column

• Replacing all values in an existing column

• Finding and replacing values in an existing column

• Finding and populating empty fields

Creating a new column

To create a new column in the data extract, follow these steps:

1. Under the Configuration tab, in the Choose an action drop-down menu, select Create new column.

2. (Optional) Set up rules and define criteria that find specific values within existing columns. The results of this search criteria are used to populate the target column. For more information on setting up rules, see Adding rules to the Create or Edit Columns transformation.

3. In Name new column, enter the name of the target column to add to the data extract. For example, enter sub_1000_clicks as a new column name.

4. In Value, enter the value to populate the target column. This value can be one, or a combination, of the following:

   Static value

   Enter text to populate the target column. For example, enter the value new to populate every value in the column with new.

   Existing column values

   In the Value field, click + Add existing values and select the column that contains the values to populate the target column. You can select multiple columns.

   Static values and values from existing columns

   Combine static values with existing column values to create a new value to populate the target column. For example, you can append a static value of _UK to an existing column such as account_name to create account_name_UK. Use special characters such as _ or - to combine multiple existing columns into a single value. For example, column1_column2.

As a result, this transformation will add a new column to the data extract that is populated with the values entered in the Values field.

Replacing all values in an existing column

To replace the entire contents of an existing column in a data extract, follow these steps:

1. Under the Configuration tab, in the Choose an action drop-down menu, select Edit existing column.

2. In Column to edit, select the existing column in the data extract that contains the values to edit.

3. (Optional) Set up rules and define criteria that find specific values within existing columns. The results of this search criteria are used to populate the target column. For more information on setting up rules, see Adding rules to the Create or Edit Columns transformation.

4. In Editing type, select Replace entire cell content.

5. In Replace with, enter the new value with which to replace the contents of the target column. You can enter a static value, select values from an existing column, or combine these two options.

   Static value

   Enter text to populate the target column. For example, enter the value new to populate every value in the column with new.

   Existing column values

   In the Value field, click + Add existing values and select the column that contains the values to populate the target column. You can select multiple columns.

   Static values and values from existing columns

   Combine static values with existing column values to create a new value to populate the target column. For example, you can append a static value of _UK to an existing column such as account_name to create account_name_UK. Use special characters such as _ or - to combine multiple existing columns into a single value. For example, column1_column2.

As a result, this transformation will replace the values in the selected existing column in the data extract with the values entered in the Replace with field.

Finding and replacing values in an existing column

To find and replace specific values within existing columns of a data extract, follow these steps:

1. Under the Configuration tab, in the Choose an action drop-down menu, select Edit existing column.

2. In Column to edit, select the existing column in the data extract that contains the values to edit.

3. (Optional) Set up rules and define criteria that find specific values within existing columns. The results of this search criteria are used to populate the target column. For more information on setting up rules, see Adding rules to the Create or Edit Columns transformation.

4. In Editing type, select Find and replace part of cell.

5. In Find part, enter the value to be replaced.

6. (Optional) Select Case-sensitive to make the search criteria case sensitive.

7. In Replace with, enter the new value with which to replace the found value. You can enter a static value, select values from an existing column, or a combination of these two options.

   Static value

   Enter text to populate the target column. For example, enter the value new to populate every value in the column with new.

   Existing column values

   In the Value field, click + Add existing values and select the column that contains the values to populate the target column. You can select multiple columns.

   Static values and values from existing columns

   Combine static values with existing column values to create a new value to populate the target column. For example, you can append a static value of _UK to an existing column such as account_name to create account_name_UK. Use special characters such as _ or - to combine multiple existing columns into a single value. For example, column1_column2.

8. To add another find and replace condition, click and repeat the steps above.

As a result, this transformation will find and replace the values specified in the Find part field in the selected existing column in the data extract with the values entered in the Replace with field.

Finding and populating empty fields

Use the Create or Edit Columns transformation to find empty fields in your data extract and populate them with a value of your choice. To find and populate empty fields, follow these steps:

1. Under the Configuration tab, in the Choose an action drop-down menu, select Edit existing column.

2. In Column to edit, select the existing column in the data extract to search through and find the empty fields.

3. Select the Add rules to build your instruction toggle.

4. In the WHEN section, configure the following fields:

   Column

   Select the column to search through and find empty fields.

   Operator

   From the drop-down field to the right of the Column field, select is empty.

5. In the THEN section, configure the following fields:

   Editing Type

   From the drop-down field, select Replace entire cell content.

   Replace with

   Enter the value with which to populate the empty fields. (Optional) Click + Add existing values to populate the empty fields with values from another column.

6. (Optional) To add another column to search through, click + Add condition.

7. In the OTHERWISE section, in Editing type drop-down field, select Leave original values.

As a result, this transformation will find empty values in the selected columns and populate them with a given value.