Edit Values

This transformation step allows you to apply rules that specify how the values in the data are modified. You can also apply a filter to the rules to modify only the values that meet the filter criteria.

The scope of editing covers:

• adding a suffix/prefix to the values
• replacing the value with:
  • a fixed value (selected values are replaced with a defined string)
  • a dynamic value (the result of a Jinjava insert)
  • values from another column
  • UUID (UUID v4)
  • current date

Step configuration

1. In the Data Transformation Studio, add the Edit values step.
2. Indicate the columns whose values will be edited.
3. From the Edit values by dropdown list, select one of the following options:
   • Adding prefix - Select this option if you want to add a group of characters at the beginning of a value in a column, for example, applying a +48 prefix to the phone column transforms its values into values such as +487717678890
   • Adding suffix - Select this option if you want to add a group of characters to the end of a value, for example, applying a , Ph.D. suffix to the surname column transforms its values into values such as Woods, Ph.D.
   • Replacing - Select this option if you want to entirely change the value with:
     • Static value - This option replaces the original value with the one you enter. All values in the selected columns are replaced, unless you narrow down the scope with the If current value filter (step 4). For example, to change John to Jonathan, enter Jonathan and add the If current value filter with the Equal operator and the John value.
     • Dynamic value - This option replaces the original value with the result of a Jinjava insert. You can only refer to the data in the transformed file, for example, by using root['columnName']. Inserts that retrieve data from Synerise, such as profile attributes, aggregates, and expressions, aren't supported.
       For example, the {{ root["name"]|replace("Bob", "Robert") }} insert replaces all occurrences of Bob in the name column with Robert. The entire value is replaced with the result of the insert, which in this case is the original value with Bob changed to Robert.
       For the list of inserts you can use, see Data transformation inserts.
     • Column - This option replaces the original value with values from a column you select.
     • UUID - This option replaces the column values with a randomly generated UUID v4 (unique for each row).
     • Current date - This option replaces the original value with the current date (the date and time are generated on an ongoing basis during the transformation). You can select the timezone and date format.
4. Optionally, to narrow down the scope of changes to the values which meet a condition, click If current value and define the filter. The comparison is case-sensitive, for example, john doesn't match John
   1. From the dropdown list, select a logical operator:
      • Equal - the transformation is performed on the values which fully match the string you provide.
      • Not equal - the transformation is performed on the values which are different from the string you provide.
      • Ends with - the transformation is performed on the values which end with a character or sequence you provide.
      • Starts with - the transformation is performed on the values which start with a character or sequence you provide.
      • Contain - the transformation is performed on the values which contain a substring or character you provide.
      • Not contain - the transformation is performed on the values which do not contain a substring or character you provide.
   2. In the field next to the dropdown list, enter the value.

   

   To remove the filter, click the red cross icon next to it.

5. To add more rules, click Add rule and repeat steps 2-4.
6. To define what happens if rows contain errors, from the dropdown list next to Add rule, select one of the error handling options.
7. Confirm the settings by clicking Apply.
8. 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.

Current date option

Using the Current date option, you can enrich your data with a dynamically injected date and time which can be used, for example, to build an event salt. To define a rule with Current date:

1. Select a timezone.
2. Select one of the available formats:
   • ISO (for example 2022-10-05T05:55:16.786596Z)
   • Timestamp (for example 1664949342323)
   • MMM d, YYYY (for example Oct 5, 2022)
   • MMM d, YYYY, HH:mm:ss (for example Oct 5, 2022, 05:57:14)
   • YYYY-MM-dd HH:mm (for example 2022-10-05 05:57)
   • Custom - you can create your own date and time format.

Custom date format

You can use the following elements to build a custom format. The example values use October 05, 2022 as the date.

• MMM - name of month (Oct)
• MM - number of month (10)
• DD - day of year (278)
• dd - day of month (05)
• YYYY - year (2022)
• YY - year (22)

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

In this example, all values that start with +48 in a given column are replaced with an empty value.

1. In the Data Transformation Studio, add sample data.
2. Click Add step and select Edit values.
3. From the Select column dropdown list, select the column to be transformed.
4. From the Edit values by dropdown list, select Replacing, then select Static value and leave the value field empty.
5. Click If current value, select Starts with, and enter +48
6. Confirm the changes by clicking Apply.
   Result: In the Output tab, all values in the column that began with +48 are empty.