3.4 Data Cleansing Tool

3.4 Data Cleansing Tool

Core Concept: The Data Cleansing tool is a macro-based utility located in the Preparation palette. It performs standardized data sanitation operations across multiple columns simultaneously without requiring a single line of formula code. It cleanses null values, trims extraneous whitespace, strips unwanted character sets (letters, digits, punctuation), and standardizes text casing in a single execution step.

---

1. Tool Architecture & Multi-Field Selection

Unlike the Formula tool—which operates one expression at a time—the Data Cleansing tool is engineered for batch processing. You can configure a single Data Cleansing tool to sanitize 50 columns across a dataset in parallel.

┌────────────────────────────────────────────────────────────────────────────────────────┐
│ Data Cleansing Tool Configuration Interface                                            │
├────────────────────────────────────────────────────────────────────────────────────────┤
│ Select Fields to Cleanse:                                                              │
│ [✓] (All)       [ ] Customer_ID    [✓] First_Name    [✓] Last_Name    [✓] Balance_Due  │
├────────────────────────────────────────────────────────────────────────────────────────┤
│ Remove Null Data:                                                                      │
│   [✓] Replace with Blanks (String Fields)                                              │
│   [✓] Replace with 0 (Numeric Fields)                                                  │
├────────────────────────────────────────────────────────────────────────────────────────┤
│ Select Unwanted Characters:                                                            │
│   [✓] Leading and Trailing Whitespace                                                  │
│   [ ] Tabs, Newlines, and Duplicate Whitespace                                         │
│   [ ] All Whitespace                                                                   │
│   [ ] Letters       [ ] Numbers       [✓] Punctuation                                  │
├────────────────────────────────────────────────────────────────────────────────────────┤
│ Modify Case:                                                                           │
│   (•) Title case    ( ) Upper case    ( ) Lower case    ( ) None                       │
└────────────────────────────────────────────────────────────────────────────────────────┘

Field Selection Capabilities

• (All) Checkbox: Selects every field in the incoming stream. As new upstream columns enter the workflow, they are automatically included in the cleansing routine.
• Individual Checkboxes: Allows selective targeting of specific string or numeric fields.
• Heterogeneous Type Handling: If both string and numeric fields are checked, Designer applies string operations (e.g., casing, blank replacement) only to string columns, and numeric operations (e.g., replace with 0) only to numeric columns without throwing errors.

---

2. Null Data Replacement Mechanics

Missing values ([Null]) cause aggregation errors, join dropouts, and calculation failures. The Data Cleansing tool provides standardized, type-safe null remediation:

Incoming Raw Field                 Cleansing Configuration Option             Cleaned Output
─────────────────────────────────────────────────────────────────────────────────────────────
First_Name: [Null]   (String)   ──► [✓] Replace with Blanks (String Fields) ──► First_Name: ""
Balance_Due: [Null]  (Numeric)  ──► [✓] Replace with 0 (Numeric Fields)     ──► Balance_Due: 0
First_Name: [Null]   (String)   ──► [✓] Replace with 0 (Numeric Fields)     ──► First_Name: [Null] (No Change)

1. Replace with Blanks (String Fields): Converts [Null] values in string columns to empty strings (""). Numeric and Date columns are completely unaffected.
2. Replace with 0 (Numeric Fields): Converts [Null] values in Byte, Int16, Int32, Int64, FixedDecimal, Float, and Double columns to 0. String columns are completely unaffected.
3. Both Options Checked: Safely standardizes all incoming fields simultaneously—strings become "" and numbers become 0.

---

3. Whitespace Removal Options (High-Yield Exam Topic)

Extraneous whitespace introduced during manual data entry or database extraction is one of the leading causes of join failures (e.g., "TX " != "TX"). The Data Cleansing tool offers three distinct whitespace removal settings:

Incoming String           "   Austin ,   TX   \n  78701   "
────────────────────────────────────────────────────────────────────────────────────────
1. Leading & Trailing     "Austin ,   TX   \n  78701"     (Trims outer padding only)
2. Tabs, Newlines, Dups   "   Austin , TX 78701   "       (Collapses interior whitespace)
3. All Whitespace         "Austin,TX78701"                (Strips every space character)

1. Leading and Trailing Whitespace

• Operation: Strips space characters and tabs from the beginning and end of a string while leaving all internal spacing untouched.
• Example: " Alteryx Designer " $\rightarrow$ "Alteryx Designer".
• Best Practice: This is the most common and safest cleansing option for names, addresses, and product descriptions.

2. Tabs, Newlines, and Duplicate Whitespace

• Operation: Replaces tabs (\t), carriage returns (\r), line feeds (\n), and multiple consecutive spaces with a single space.
• Example: "Part\t\t#\n\n100 A" $\rightarrow$ "Part # 100 A".
• Best Practice: Essential for cleaning unformatted comment blocks, customer notes, and freeform survey responses.

3. All Whitespace

• Operation: Completely strips every single space, tab, and newline from the string.
• Example: "John Smith" $\rightarrow$ "JohnSmith".
• Exam Trap: Never select "All Whitespace" on names or street addresses unless your explicit goal is to concatenate words! It is intended for account numbers, phone numbers, and postal codes (e.g., "1 800 555 0199" $\rightarrow$ "18005550199").

---

4. Unwanted Character Removal & Case Modification

Character Class Filtering

The tool can purge entire character classes across selected string columns:

• Letters: Strips all alphabetical characters ([a-zA-Z]).
  • Example: "Account #4928-B" $\rightarrow$ " #4928-".
• Numbers: Strips all digit characters ([0-9]).
  • Example: "Suite 400" $\rightarrow$ "Suite ".
• Punctuation: Removes all standard punctuation marks (!"#$%&'()*+,-./:;<=>?@[\\]^_`{|}~).
  • Example: "Dallas, TX.", with Punctuation & Leading/Trailing Whitespace checked $\rightarrow$ "Dallas TX".

Case Standardization

Standardizes capitalization across text fields:

• Upper case: Converts all text to capital letters ("austin, tx" $\rightarrow$ "AUSTIN, TX").
• Lower case: Converts all text to lowercase ("Admin@ACME.org" $\rightarrow$ "admin@acme.org").
• Title case: Capitalizes the first letter of each word ("san francisco, ca" $\rightarrow$ "San Francisco, Ca").

---

5. Execution Pipeline & Order of Operations

When a record passes through the Data Cleansing tool, operations are applied in a strict internal sequence:

┌─────────────────┐     ┌───────────────────┐     ┌───────────────────┐     ┌───────────────────┐
│ 1. Replace      │ ──► │ 2. Remove         │ ──► │ 3. Remove         │ ──► │ 4. Modify         │
│    Null Data    │     │    Unwanted Chars │     │    Whitespace     │     │    Case           │
└─────────────────┘     └───────────────────┘     └───────────────────┘     └───────────────────┘

---

6. Data Cleansing Tool vs. Formula Tool: Strategic Selection

Understanding when to use the Data Cleansing tool versus the Formula tool is a core exam objective.

Exam Tip: Use the Data Cleansing Tool for fast, multi-column baseline hygiene (trimming whitespace, zeroing nulls, Title Casing). Use the Formula Tool when you need conditional logic, mathematical computations, or need to preserve the original field while generating a new _Cleaned field.