Transformation Activity (2.9)
Introduction
A transformation activity transforms fields in a single staging table. For each field that is transformed, you can either overwrite the existing field or set the value of a new field.
Options
For each field to be transformed, the following options apply:
-
Function Type—Specifies the function to use to transform the value. See Transformation Functions.
-
Overwrite—Specifies whether to replace the old value. In this case, the system will not generate a new field in the staging table.
If Overwrite is cleared, the system writes the new value into the field named by Result Field Name.
-
Transformation Name—Specifies a short name for this field transformation.
-
Result Field Name—Specifies field that will store this transformed value (if Overwrite is not selected). The system automatically adds a field to the staging table; the actual name of the new field is %T_newname where newname is the name you specified.
-
Result Field Type—Specifies the type for the new result field.
There are additional options depending on Function Type; see the next section.
Transformation Functions
The available transformation functions are as follows:
Available for string fields. This function transforms the data by using a code table, which provides a set of key/value pairs. If the value being converted matches a key in the given code table, that value is converted to the value associated with that key. Also specify:
-
Code Table Name—Specify the name of a table that contains key/value data.
-
Code Table Key Column—Specify the name of the field in that table that contains the keys.
-
Code Table Value Column—Specify the name of the field in that table that contains the associated values.
Available for string fields. This function transforms the data by using an SQL expression. Also specify SQL Expression as an SQL expression that returns a single value, such as an expression that combines field names and SQL operators. You must delimit any field name that is an SQL reserved word.
Available for string fields. This function converts the string to lowercase.
Available for string fields. This function performs a substring replacement. Also specify:
-
Old Substring— Type the text to be replaced.
-
New Substring— Type the replacement text.
Available for string fields. This function trims whitespace (leading, trailing, or both). Select Trim Leading Whitespace, Trim Trailing Whitespace, or both.
Available for string fields. This function trims a specified set of characters (leading, trailing, or both). Specify one or more characters for Characters To Trim. Then select Trim Leading Characters, Trim Trailing Characters, or both.
Available for string fields. This function converts the string to uppercase.
Available for numeric fields. This function rounds the number as specified. Also specify Scale, the number of decimal places.
