Cleanse Node

The Cleanse node is used when column data need to be replaced (in whole or in part), rounded, trimmed, or obfuscated based on one or more user-defined conditions. This node has three main tabs that can be used to accomplish these operations: Replace, Round and Modify.

Replace Tab

The Replace Tab works like a "Find and Replace" operation. It allows users to completely replace the contents of a selected field based upon if the original value meets the user-defined criteria. To configure a Cleanse operation, select a column from the columns list on the left as the variable to replace values within. Then define the criteria the value needs to meet if it is to be replaced using the following operators:

• Equals (=)
• Not Equals (<>)
• Greater Than (>)
• Greater Than or Equal To (>=)
• Less Than (<)
• Less Than or Equal To (<=)
• In (specified list)
• Not In (specified list)
• Contains (found within column value)
• Does Not Contain (not found within column value)
• Starts With (the specified value)
• Ends With (the specified value)
• Is Missing (no value exists; may be filled with the value of an earlier, lagging record)
• Always (always replace with the value of an earlier, lagging record)

Finally, define what the value should be replaced with if it meets the above criteria in the Replace with window, or use the check box to replace the specified values with Null. Select Add to save the replace action. When the defined criteria are met, Cleanse will overwrite the existing data with the new value.

Note: As many Cleanse operations can be defined within one node as required. 

Round Tab

When working with columns that contain numerical values, additional operations are available within the Round Tab. These include:

• Round up
• Round down
• Round away from zero
• Round to the nearest (even)

Modify Tab

The Modify Tab offers three distinct operations that can be performed only on columns containing text values:

• Trim: Adding a Cleanse using this option will remove all leading and trailing blanks from fields in the chosen column.
• Obfuscate: Obfuscate will replace the contents of the chosen column with a series of hash characters. Note that identical entries in the original data will have identical obfuscation entries.
• Sub-string Replacement: This operation allows users to replaces a portion of a text string with a new set of characters as opposed to replacing the entire text string.

Tips

Multi-Selecting Fields

If the same Cleanse action needs to be performed on several columns, hold the Ctrl key and multi-select all the columns that require the cleanse operation from the column list on the left. Then define the cleanse criteria, and select Add. Once added, a separate entry for each column will appeared in the list of Cleanse actions. Note that the Get Values button is disabled when multiple columns are selected and that all columns selected must be of the same data type (text, integer, real, etc.).

Tip: If multi-selecting columns is not working, use a Convert node prior to the Cleanse node to change the data types to match.

Cleanse Operation Order

The order that the cleanse operations are performed is based on their order in the list at the bottom of the setup window, which is the order they were added. This may be changed by selecting one or more operations then using the black arrow keys to rearrange the list. The order the cleanse actions occur is compounding meaning that the first operation in the list is executed, and then the second operation applies it's action to the result of the first operation.

Updating or Deleting Cleanse Operations

A cleanse operation may be edited by selecting it from the list, making the desired changes, then clicking the Update button. To delete an existing operation, select the operation and use the red X on the left side of the window. 

Video Tutorial