Custom Transformations

About Custom Transformations

You can replace the field values coming from a source using a variety of custom transformations.

Defining Custom Transformations

Data transformation is part of the job’s data mapping configuration.

There are two ways you can access data mapping:

• When creating a job, data mapping is available as one of the steps.
• When updating a scheduled job, data mapping is available from the job’s actions menu.

Once you open the job’s data mapping configuration, perform the following steps:

1. Locate the field you wish to transform.
2. Click the gear icon in the Transformations column.
   
   Qtip: You can apply only one type of transformation to a field. A subscript next to the gear icon indicates that a transformation is already applied to a field. When you apply another transformation, any previously defined transformation is removed.
3. Select Custom Transformation from the dropdown menu.
4. Click on the field you want to transform from the Fields section and drag it into the Expression box.
   Qtip: You can filter the fields by type or by name.
5. Click on the function from the Functions section and drag it into the Expression box. To learn more about the available functions and their syntax, please see Building Expressions.
   Qtip: You can filter the functions by type or by name.
6. Adjust the expression in the Expression box so that its syntax is correct.
7. The Data Type drop-down list indicates field data type. If this is a new attribute, you can edit the data type of the field (the changes will be displayed on the main data mapping screen).
8. Once the transformation is defined, enter an example from your dataset into the Test Value box and click the Test button.
9. The result of the transformation is displayed in the box next to the “Test” button.
10. When you are finished, click Save.
11. Depending on how you accessed data mapping, either continue creating a job or save your updates to a scheduled job.

Transformation Examples

This section provides examples of using custom transformations to replace and modify source field values.

Assign a Custom Source Value

Goal: Assign a custom text value (“April Surveys”) to the “Source” attribute.

How: Apply the following transformation to the “Source” field (simply put the text value inside quotation marks):

Change “Last Name,First Name” to “First Name Last Name”

Goal: Change the name format from “last name,first name” to “first name last name” and capitalize the first letter of every word.

How: Apply the following transformation to the name field:

PROPERCASE(REPLACEBYREGEXP(NAME, “(.*),(.*)”, “$2 $1”))

Convert a Date from UTC to CST

Goal: Convert a date field from a UTC time zone to CST.

How: Apply the following transformation to the END_DATE field:

CONVERT_TO_TIMEZONE(END_DATE, “Etc/UTC”, “America/Chicago”)

Convert hh:mm:ss to Minutes

Goal: Convert time from the hh:mm:ss format to the number of minutes.

How: Apply the following transformation:

GETMINUTESBETWEEN(TEXTTODATE(“1970-01-01”, “YYYY-MM-DD”), CONCAT(“1970-01-01T”, MY_TIME))

Convert Unix Epoch Time in Seconds to ISO 8601

Goal: Convert a Unix epoch timestamp in seconds to ISO 8601 date format.

How: The following transformation does two things: it turns seconds into milliseconds and then turns the milliseconds into an ISO 8601 date:

NUMBERTODATE(TEXTTONUMBER(CONCAT(MY_DATE, “000”)))

Derive Value Based on Whether Something Is in an Array

Goal: Check the state name and assign a “DMV Area” value if it matches any of the listed states. Otherwise, set as “Other States.”

How: Add a custom text field called AREA and apply the following transformation:

IF(LOWER(STATE) in [“md”, “dc”, “va”], “DMV Area”, “Other States”)

Extract Article Name from a Page Title

Goal: Extract the name of the article after the last “|” divider in a page title.

How: Apply the following transformation to the page title field:

TRIMRIGHT(MID(PAGE_TITLE,FINDLAST(PAGE_TITLE,”|”)+1,1000))

Join First and Last Name into a Single Field

Goal: Combine FIRST_NAME and LAST_NAME fields into a new FULL_NAME field and capitalize the first letter of every word.

How: Add a custom text field called FULL_NAME and apply the following transformation:

PROPERCASE(CONCAT(FIRST_NAME, ” “, LAST_NAME))

Join Two Verbatims and Load Only the Combined Verbatim

Goal: Join two verbatim fields VERBATIM1 and VERBATIM2 into a new VERBATIM3 field and load only the combined verbatim into XM Discover.

How: Perform these steps:

1. Create a verbatim field called VERBATIM3 and apply the following transformation to it:CONCAT(VERBATIM1, ” “, VERBATIM2)
2. For VERBATIM1 and VERBATIM2 fields, set Data Type to Do Not Map.

Multi-Variable Rollup

Goal: Create a multi-variable rollup for answers to a single question that are split across several attributes. Suppose the dataset contains multiple “yes or no” attributes to indicate different levels of membership a customer may have:

• QID2_1 for Silver
• QID2_2 for Gold
• QID2_3 for Platinum

You can roll all the “yes” answers into a single new attribute to indicate the customer’s membership level.

How: Apply the following transformation:

TRIMRIGHT(CONCAT(IF(QID2_1==”Yes”, “Silver”, “”), IF(QID2_2==”Yes”, “Gold”, “”), IF(QID2_3==”Yes”, “Platinum”, “”)))

Remove Leading Character

Goal: Remove the first character in a string.

How: Apply the following transformation:

REPLACEBYINDEX(MY_NUMBER, 1, 1, “”)

Trim Leading Zeros

Goal: Trim leading zeros in a number.

How: For a text value, apply the following transformation:

CONCAT(TEXTTONUMBER(MY_NUMBER), “”)

For a numeric value, apply the following transformation:

TEXTTONUMBER(MY_NUMBER)