Whether data is missing, mismatched, or simply wrong, you can use a variety of methods in the
|D s webapp|
In the Transformer page, you can use the following methods to replace values:
Select a value in the data grid to prompt a series of suggestions on what to do with the data. Typically, replacement options are near the top of the suggestions.
|By column menu||From the menu to the right of the column, select Replace and a sub-menu item to begin configuring a replacement transformation. See Column Menus.|
|By Transformer toolbar||At the top of the data grid, click the Replace icon in the Transformer toolbar to begin configuring replacements. See Transformer Toolbar.|
|By Search panel||In the Search panel, enter |
Replace by selection
When you select data in the data grid, the replacement suggestions are pre-specified for you, including a number of variants available in the suggestion card.
For more information, see Overview of Predictive Transformation.
For privacy reasons or for sensitivity reasons, you may wish to mask sensitive data in one or more columns with fixed strings.
Delete whole column(s)
If you need to remove the data in an entire column, the easiest method is to delete a column. Select one or more columns and then select Delete from the column drop-down. See Remove Data.
Masking all values
You can use a transformation like the following to replace all values in a column with a simple string. In this case, the value
#REDACTED# has been inserted in place of all values in the column.
Partial masking of values
Suppose you wish to partially mask data in a column. In the following example, data for the
AcctNum column is masked, except for the last four characters (digits):
Mask multiple columns based on data type
You can use the following type of transformation to hide data based on data type. In this example, the values in all columns with Social Security Number (SSN) are replaced with a masking value:
Replace with values from another column
Replace whole column
You can do simple replacements of data from one column into another with transformations like the following. In this example, the values of
colB are replaced with the values of
0.15 added to them:
Replace partial values from another column
You can use the
MERGE function to blend full or partial sets of columns into a new column. In the following example, the
newBrandId value is concatenated with the product code in the
ProdId column to create a new product identifier:
Replace between positions
You can perform replacements based on character positions that you specify as part of the transformation.
Search and replace text or pattern
You can search and replace content in your dataset based on literals or patterns. In the following example, the value
##CLT_NAME## is replaced with
Our Customer, Inc. across all columns in the dataset:
Replace missing values
Replace missing with zeroes
For numeric data, you may choose to replace values that are missing in a column with zeros. The following transformation sets missing values in the
DiscountPct columns of Decimal data type to
Replace missing with average values
One of the problems with the above method is that any statistical computations applied to the column are now affected by the zeroing of the missing values. For example, the computation for the
AVERAGE function does not factor in missing values into the count of rows, which result in skewing of values for your purposes.
In this manner, the new column can be used for some statistical modeling, while preserving the original values in the original column.
For more information, see Rename Columns.