The Cleanse node allows you to perform a column value cleaning or look up related data in a source, and return the data for use in downstream processing. To return many related records, the matching data is returned in array attributes. You can then choose to split this data (effectively creating a join across the working data and the Reference Data) using the Split node on these array attributes.
Following are the steps to apply the Cleanse node in a pipeline:
Drag and drop the Cleanse node from the Transformations section to the pipeline editor page.
Connect the source node to the Cleanse node.
Double-click the Cleanse node. The properties page is displayed.
Click Add Cleanse Transformation, enter the following details and click Save:
Field | Description |
|---|---|
Transformation Type | The type of Cleanse transformation includes Generic Lookup and Lookup. |
Cleanup Column | The column on which the cleanup will be performed. |
Lookup Table | The source table to lookup for related data. |
Lookup Column | The lookup column which must be compared with cleanup column. For Lookup transformation type, the first column will the automatically considered as the lookup column. |
Value Column | The value column. For Lookup transformation type, the second column will the automatically considered as the value column. |
Following are the two types of Cleanse transformations:
Generic Lookup: In generic lookup transformation, you must select cleanup column and a lookup table. Based on the lookup table selection, the drop-down lists for the lookup and value columns are displayed. Select the required lookup and value columns.
Lookup: In lookup transformation, you must select cleanup column and lookup table. By default, the first and second columns will be considered as lookup and value columns respectively.
NOTE For details on derivations, see Derivations.