Data Cleansing Rules
Last Updated: Mar 15, 2021
Last Updated: Mar 15, 2021
The various conditions available for Data Cleansing are explained below.
| Conditions | Description | Inputs | Example |
|---|---|---|---|
| Add Number | Add a number to the value | Number to be added with | Add Number (5): Adds 5 to the value |
| Append | Add a string to the end of the value | Text to be added at the end | Append(s) Converts the value Item to Items |
| Data present after | Extracts the data present after the string specified | String | Data present after(Mr.) Displays the name John after Mr. |
| Data present before | Extracts the data present before the string specified | String | Data present before(rupees) Displays the amount 1000 before rupees |
| Extract Date | Extracts the value of date from the value field | No inputs | Extracts 28 from 28-March-2021 18:30:01 |
| Extract Month | Extracts the month number from the value field | No inputs | Extracts 3 from 28-March-2021 18:30:01 |
| Extract Year | Extracts year from the value field | No inputs | Extracts 2021 from 28-March-2021 18:30:01 |
| Left Strip | Removes leading whitespace (on the left) | No inputs | Removes leading whitespaces |
| Lower | Change the text to lower case | No inputs |
Changes the text to lowercase |
| Multiply Number | Multiply the value with the specified number | Number to be multiplied with | Multiply Number(2): Multiplies the value with 2 |
| Negative of Number | Converts the value to a negative number | No inputs | Converts 2 to -2 |
| Prepend | Add a string at the beginning of the value | Text to be added at the beginning | Prepend(e) Converts the value mailid to emailid |
| Remove | Remove a section of the text | Text to be removed | Remove(Smith): Removes Smith from the name John Smith |
| Replace None | Converts the replaced text to the text which was present before replacing | No inputs | Text before replacing is retained |
| Replace text | Replace a section of text with the specified text | Text that has to be replaced and Text to be replaced with | Replace(John Smith, Peter John): Replaces John Smith with Peter John |
| Replace regex | Replace a section of text with the specified text that matches the regular expression pattern |
Regular expression to be used to replace the text and Text to be replaced with | Replaceregex(\s+, ) Replaces multiple spaces between text characters with single space |
| Right Strip | Removes the trailing whitespace (on the right) | No inputs | Removes trailing whitespace |
| Sub String | Display the substring of a value | Position from where the sub string must start and Total number of characters to be displayed |
Substring(1, 4) Converts the value PETER to ETER |
| Subtract Number | Subtracts the value with the specified number | Number to be subtracted with | Subtract Number(5): Subtracts 5 from the value |
| To Date | Change the date format | Current date format and Expected date format | To Date(25/2/21, 25/02/2021) Converts 25/2/21 to 25/02/2021 |
| To String | Converts the value to a string | Length of the string | To String(5) Converts June1981 to June1 |
| Upper | Change the text to upper case | No inputs |
Changes the text to uppercase |
| Trim Whitespaces | Trim the spaces in the beginning and the end of the value field |
No inputs | Trim whitespaces |