Merge Columns

This transformation step allows you to create a new column based on the values from other columns and to define the separator between the merged values.

Step configuration

1. In the Data Transformation Studio, add the Merge columns step.
2. Indicate the columns to merge.
3. In the Merge values to new column field, enter the name of the column that will be added.
4. In the separate values with field, enter the separator to put between the merged values.
5. Optionally, to delete the original columns after merging, enable the Remove original columns toggle.
6. To add more rules, click Add rule and repeat steps 2-5.
7. To define what happens if rows contain errors, from the dropdown list next to Add rule, select one of the error handling options.
8. Confirm the settings by clicking Apply.
9. You can check the result in the Output tab.

Selecting columns

In each rule of the step, you indicate the columns on which the operation is performed.

1. In the header of the rule, click these and select one of the options:
   • these - the operation is performed on the columns you indicate.
   • all except these - the operation is performed on all columns except the ones you indicate.
2. From the first dropdown list in the rule, select how you want to indicate the columns:
   • Columns - from the Select column dropdown list, select one or more columns.
   • Where name - define a condition (a dynamic condition) that the column names must meet. Select a logical operator (Ends with, Starts with, Contain, Not contain, or Regex) and enter the value. For example, if you select Starts with and enter pro, the operation is performed on the columns whose names start with pro
     Column names are case-sensitive, for example, product doesn't match ProductCode. A regular expression must match the entire column name, for example, product.* matches productName, but product doesn't. To ignore the letter case in a regular expression, add (?i) at the beginning, for example, (?i)product.*

You can't combine dynamic conditions with the all except these option.

Dynamic conditions are especially helpful when you use a JSON file as sample data. When a JSON file contains an object deeper than the root object, its items are transferred to the transformation as separate columns named {object}.{parameter}. You can't add those columns from the Select column list, but you can include them with a dynamic condition, for example, by including columns whose names contain the name of the object they were created from.

Handle errors

You can define what happens with rows in which the step fails (for example, as a result of Jinja rendering). From the dropdown list next to Add rule, select one of the following options:

• Skip row if error occurred - It omits the row when an exception occurs.
• Skip if result is null or empty string - It omits rows with empty strings and null (which may be the result of rendering Jinja).
• Stop further transformation - The transformation stops at the row with an error. The data before the line that caused the error is transformed (and imported if the transformation is used in the Data Transformation Studio node).
• Insert null if error occurred - Wrong values are replaced with null. If you use such a transformation for the import of profiles, the existing value of an attribute is cleared and replaced with the null value.
• Insert empty string if error occurred - Wrong value is removed and the cell remains empty.

Example of use

You can use the Merge columns step to create an event salt. Event salt allows you to avoid duplicating an entry in the database if it is imported more than once. It is especially useful while importing events to Synerise.

This example uses the following sample data:

customId,orderId,paymentInfo,finalUnitPriceAmount,productQuantity,sku,eventTimestamp,productName,productPriceAmount
123345,448362,cash,13.35,1,642,13.02.2022,shoe1,13.35
123345,448362,cash,40,1,453,13.02.2022,shoe2,40
345678,45678990,online,13.99,1,726,13.02.2022,shoe3,13.99

1. In the Data Transformation Studio, add sample data.
2. Click Add step and select Merge columns.
3. In the configuration of the step:
   1. From the Select column dropdown list, select the orderId, sku, and eventTimestamp columns.
   2. In the Merge values to new column field, enter eventSalt
   3. In the separate values with field, enter -
   4. Leave the rest of the settings at default.
4. Confirm by clicking Apply.
   Result: In the Output tab, the data contains a new eventSalt column with values such as 448362-642-13.02.2022