Standardization Methods

You can use any of the following methods for standardizing values in your dataset's columns. Depending on the situation, you may choose to mix-and-match these methods. Details on these methods are below.

Invalid Values

These standardization techniques assume that your column contains only valid or empty values. 

Standardize Values by Clustering

Using one of the supported matching algorithms, 

For more information, see Overview of Cluster Clean.

Standardize Formatting by Patterns

For individual columns, 

555-1212
415-555-1212
4155551212
(415) 555-1212
+1 (415) 555-1212

You can apply pattern-based standardization through the Patterns tab. See Column Details Panel.

Standardize Using Functions

The following functions can be useful for standardizing values. 

Functions for strings

All values can be converted to string, so these string functions can be applied to any column if its data type is converted to String data type. 

Example:

• The Columns value is a wildcard, which in this case applies the transformation across all columns in the dataset (*).

• In the Formula, you see a nested expression. If the value in the column is valid against String data type, then, do the following to the column value:

  Info
  NOTE: The IFVALID function tests each row value for validation against the specified data type. It does not test the column against the data type. See IFVALID Function.

   

  • The TRIM function removes leading and trailing whitespace, which may register as a difference between values. See TRIM Function.
  • The UPPER function then converts the output of the TRIM function to all uppercase. So, differences in capitalization are eliminated. See UPPER Function.
  • The LEFT function truncates the output of the UPPER function to a maximum of 32 characters. See LEFT Function.

The net result of this single step applied to all columns is to eliminate whitespace, convert to uppercase, and then truncate the length of each string to only 32 characters.

For more information, see Cleanse Tasks.

Functions for numbers

You can use the following functions to standardize numeric values. 

Example:

For the NUMFORMAT function, you can specify the full format to which you want the numeric values in the column to confirm. In the following example, all values that contain a decimal point and match with the Decimal (Float) type are forced to add a value before the decimal. This step converts values like .00 to 0.00, which standardizes the format of your numbers.

• The Columns value is a wildcard, which in this case applies the transformation across all columns in the dataset (*).
• In the Formula, you see a nested expression, which is a bit more complicated than the preceding String example. 
  • The outer IF function tests if the FIND function returns a non-zero value when searching each column value for the period (.). Values that match could possibly be decimals and require further evaluation:

  • If the value in the column is valid against the Decimal (Float) data type then do the following:

    • ROUND the value to two decimal points. For more information, see ROUND Function.

    • Format the value in the following manner:

      Code Block
      0.00

    • The above format includes the two decimal points to which you rounded, adding any extra zeros if they are not present in the input rounded value.
    • Additionally, another zero is inserted in front of the decimal if it is missing in the output of the ROUND function.
    • For more information on number formats, see NUMFORMAT Function.

For more information, see Normalize Numeric Values.

Functions for dates

Since dates are structured patterns of string-based data, the best approach is to begin by using the Patterns tab in the Column Details panel. See below. 

For more detailed modifications, you can specify formatting strings that are applied as part of the DATEFORMAT function to the dates in your column.

For more information including examples on the DATEFORMAT function, see Format Dates.

Custom Data Types

You can create custom datatypes to use as a form of standardization. Values in a column that do not conform to the custom type are flagged as invalid and can be triaged accordingly.

Custom type using a dictionary file

You can upload a dictionary file containing the list of accepted values for the custom type.

For more information, see Create Custom Data Types.

Custom type using a regular expression

A custom data type can be created based on a user-defined regular expression.

For more information, see Create Custom Data Types Using RegEx.