How to clean records/data using the Data Cleanser mapper step

The Data Cleanser mapper step is used if you require to manipulate and clean records/data.

Illustrated in the image below is an example of a flow where a Record Generator is the source step, data gets manipulated by using the Split step and Calculator step and then the Data Cleanser mapper step is used to do validation and transformation to the records.

The below image is an example of the data that is in the Record Generator source step. Records are stored in an array named “People”.

The Split step is used to split the records that are in the array. The array name is inputted in the Array Path field. In this example, “People” is used as an array as shown in the page below.

A Calculator step is used to join the first names and the last names together using JavaScript code as shown in line 2 in the image below. Line 3 and line 4 contains code that will delete the “first_name” field and the “last _name” field.

The code that is used in this Calculator step is below:

let inputRecord=input.record;!!inputRecord.first_name&&!!inputRecord.last_name?inputRecord.first_name+' '+inputRecord.last_name:inputRecord.first_name+inputRecord.last_name;
delete inputRecord.first_name;
delete inputRecord.last_name;
return inputRecord;

The page below shows the output of the Calculator step. Highlighted is the new field that was created in the previous step.

The Data Cleanser step has a variety of validations and transformations that can be applied to test and modify data. Type the field name in the Add New Path filed in the Validation Map section as highlighted in the below image.

Click on the Strict checkbox to ensure that all fields are present in the record with no other additional fields.

NB: Please ensure that the Transform Record checkbox is ticked in order to manipulate the data.

Multiple fields can be added to the Data Cleanser. Illustrated in the image below are the fields that require data manipulation. Click on the edit icon as shown to access the validations and transformations for each field that was inputted.

When you are editing a field, different data types and the available validations and transformations will appear.

  • Click on the checkbox next to a data type to select it. In the example below, the string data type is selected.
  • The Allow Null checkbox allows null values in the field.
  • The Allow Blank checkbox allows for blank values in the field.
  • The Required checkbox is used to make the field a required field which means that the filed has to exist in the dataset.
  • The Trash Can icon is to delete the field from the Data Cleanser mapper step. It is located on the far right of the field which is highlighted in the image below.

Validators and Transformers are added in the section shown below. Click on the dropdown button to access a list of available validators and transformers.

NB: Please scroll to the bottom of the article to view the description of each validator and transformer.

Select the required validator/transformer and it will be added to and shown in the field section. Validators are shown in green, and transformers are shown in blue. To arrange by hierarchy, click on the highlighted button and drag the validator or transformer up or down the list. Click on the red x button, highlighted in green, to remove the validator or transformer.

The Test Value button is used to test the set validations. Type the value as highlighted in the image below and click the Test Value button to test the validation.

The List validator shows a set of values that are required for a field in the format of a list. In this example, the Car field uses a list validator to do validation for the required cars as illustrated in the image below. Click the Save button to save the configuration in the Data Cleanser step.

The image below shows the validation output from the Data Cleanser step. All valid records are shown as true. Records with a ‘false’ status have an error field with the error message provided.

A ‘Map Validations’ Calculator step is used to map the valid records using JavaScript code as shown in the page below.

The output for the above code is in the image below.

Additional information: