Transformation Activity (2.12)
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:
For each field, you can create only one transformation of each transformation type. You cannot apply multiple transformations of the same type to a single field. For example, you can apply only one Uppercase String transformation to a given field.
Available for numeric fields. This transformation groups values into ranges or categories. Also specify Buckets in square brackets ([]) delimited by commas. The bucket list will be used to classify this field value. For example, providing [:–10],[-9:0],[11:20],[31:] will group your data into those ranges without providing specifics of where exactly in the range it falls; in this example, any value that is greater than 31 will be categorized into the [31:] bucket.
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 date fields. This function adds a random but consistent number of days (between –30 and 30) to the field. The shift is consistent for a given value, preserving relative intervals and seasonality while obscuring exact dates. After this transformation, original values cannot be recovered within Data Studio.
Available for string fields. This function applies a deterministic cryptographic hash function to a value. This transformation is irreversible unless a separate re-identification mapping is maintained.
Available for string fields. This function converts the string to lowercase.
This transformation obscures parts of values for partial visibility. It is not join-safe, as it may create duplicates, and should not be used for primary join keys or identifiers when deterministic joins are required. Also specify:
-
Masking Type —Specify the type of masking you would like to be applied to your string. Select one of the following: Default, which masks all characters with a Masking Character, which you will specify after selecting this option, Random Numbers, which masks each number with a random number, Email, which only shows the first letters of the username and domain, or Partial, which shows first prefix characters followed by custom padding followed by last suffix characters, which you will specify after selecting this option.
Available for numeric fields. This function adds small random noise to numeric values.
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 numeric fields. This function rounds the number as specified. Also specify Scale , the number of decimal places.
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.
