Tool Reference Guide

Input

Input Data

Reads data from files (Excel, CSV) or DBs.

Browse

Browse

Visualizes data quality profiles & results.

Select

Select

Rename, reorder, change types, remove fields.

Cleanse

Data Cleansing

Fixes nulls, whitespace, and case issues.

Filter

Filter

Splits data: T (True) and F (False).

Impute

Imputation

Replaces numeric nulls with mean/median/mode.

Formula

Formula

Creates/updates columns using logic.

RecID

Record ID

Assigns a unique ID (1, 2, 3...) to rows.

DT

DateTime

Converts String ↔ DateTime formats.

DateTime Specifiers

Specifiers always begin with a percent sign (%), followed by a case-sensitive letter or number.

Access the complete guide: Official Reference

Year

SpecifierOutputInput Support
%yLast two digits ("22")Up to 4 digits (1959-2058)
%YFour digits ("2022")Two or four digits

Month

SpecifierOutputInput Support
%b / %hAbbrev. Name ("Sep")Abbrev. month name
%BFull Name ("September")Full or Abbrev. name
%mMonth Number (01-12)1 or 2 digits

Day

SpecifierOutputInput Support
%dDay of Month ("01")1 or 2 digits
%eDay of month (space)1 or 2 digits
%jDay of Year (001-366)3-digit day of year

Day of Week

SpecifierOutputInput Support
%aAbbrev. Weekday ("Mon")Valid abbrev.
%AFull Weekday ("Monday")Valid name

0. Setup & Objectives

File: Cab Company Data.xlsx
Context: You are an analyst cleaning real-world ride data (Ride4Me).

Learning Objectives:

  • Detect data quality issues (nulls/empty/whitespace/case).
  • Fix whitespace + capitalization inconsistencies.
  • Decide how to handle missing values (remove vs impute vs conditional replace).
  • Create a unique record identifier.
  • Convert messy date-time strings into real DateTime.
  • Filter to create weekend vs weekday subsets.
  • Understand why Null ≠ Empty ≠ Zero.

1. Input Data Input

Action: Drag the Input Data tool to the canvas and connect "Cab Company Data.xlsx".

Observe:

  • The data quality bar has multiple colors (Green=OK, Yellow=Null, Red=Leading/Trailing Whitespace).
  • Some “red flags” appear in [start_point] (whitespace issues).

2. Browse Browse

Action: Connect Browse tool to Input Data. Run.

Inspect:

  • Click field [start_point]: See leading/trailing whitespace indicators (Not OK).
  • [Cab Company]: "Ride4Me" vs "RIDE4ME" (capitalization issues).
  • [Price]: Has null values.
  • [Rain]: Mostly null.

3. Select Select

Action: Add Select tool.

  • Clean up field names (make formatting consistent).
  • Change data types where obvious (numeric fields to numeric/Double).
  • Add units to Description if desired (e.g. miles, dollars).
Important: Do NOT convert [Time Ride Ordered] to DateTime here yet. It is currently a String that doesn't match standard formats. Converting now results in Nulls.

4. Clean Categorical Cleanse

Action: Add Data Cleansing tool.

Configuration for [Cab Company]:

  • Select Field: [Cab Company]
  • Modify Case: Upper Case (Fixes Ride4Me capitalization).
  • Remove Unwanted Characters: All Whitespace.

5. Clean Whitespace Cleanse

Action: Add another Data Cleansing tool.

Configuration for [Start Point]:

  • Uncheck Replace Nulls.
  • Remove Unwanted Characters: Leading and Trailing Whitespace.

6. Replace Meaningful Nulls Cleanse

Logic: In this dataset, null rainfall doesn’t mean “unknown”; it means no rainfall. So replacing null with 0 is justified.

Action: Use Data Cleansing on [Rain].

  • Replace Nulls: Replace with 0.

7. Imputation Impute

Decision: Remove or Impute? Removing rows loses data. We will Impute missing prices.

Action: Add Imputation tool.

  • Field to impute: [Price]
  • Incoming value to replace: Null()
  • Method: Median.
Why Median? Median is safer than Mean for prices because it is less sensitive to outliers (extremely high or low values).

8. Formula (Nulls) Formula

Problem: Imputation tool is for numbers. Cab Company is text.

Action: Add Formula tool.

Expression:

IF IsNull([Cab Company])
THEN "RIDE4ME"
ELSE [Cab Company]
ENDIF

Rationale: RIDE4ME is the most frequent value (mode).

9. Unique ID RecordID

Action: Add Record ID tool.

Why? It’s possible multiple rides share the same time/company/start point, so having a single ID is cleaner.

10. DateTime Conversion DT

Action: Add DateTime tool (Parse Palette).

Configuration:

  • Format: String to Date/Time
  • Field: [Time Ride Ordered]
  • New Name: Time of Order

11. Feature Engineering DT

Action: Add another DateTime tool.

Configuration:

  • Format: Date/Time to String
  • Field: Time of Order
  • New Name: Day of Week
  • Format: Custom -> "day"

12. Filter for Weekends Filter

Action: Add Filter tool.

Logic:

Saturday OR Sunday.

[Day of Week] = "Saturday" 
OR 
[Day of Week] = "Sunday"

Anchors:

  • T (True): Records that match the condition (Weekends).
  • F (False): Everything else (Weekdays).