4.3 Sort Tool & Dictionary Sorting
Key Takeaways
- The Sort tool arranges records based on one or more columns evaluated hierarchically from top to bottom, with independent Ascending or Descending order per field.
- Default ASCII sorting is strictly case-sensitive and byte-ordered (numbers precede uppercase 'A-Z', which precede lowercase 'a-z'), whereas Dictionary Order groups characters alphabetically regardless of case.
- Null values sort to the very top (first) in Ascending order and to the very bottom (last) in Descending order.
- Empty text strings ("") sort immediately after Null values in Ascending order and immediately before Null values in Descending order.
- Sorting directly governs downstream operations in order-dependent tools such as Unique, Sample, and Multi-Row Formula.
4.3 Sort Tool & Dictionary Sorting
Core Concept: The Sort Tool (located in the Preparation palette) reorders records in a data stream based on the values of one or more specified columns. Because several critical downstream Alteryx tools (such as Unique, Sample, Running Total, and Multi-Row Formula) are order-dependent, mastering sort hierarchies, character encoding differences, and Null positioning is essential for building reliable data pipelines.
1. Sort Tool Configuration Architecture
The Sort configuration window allows multi-column sorting configurations evaluated hierarchically from top to bottom.
┌────────────────────────────────────────────────────────────────────────────────────────┐
│ Sort (18) - Configuration ▲ Up ▼ Down [-] │
├────────────────────────────────────────────────────────────────────────────────────────┤
│ Field Order │
├────────────────────────────────────────────────────────────────────────────────────────┤
│ 1. [ Region ] [ Ascending ▼ ] │
│ 2. [ Total_Sales ] [ Descending ▼ ] │
│ 3. [ Customer_Name ] [ Ascending ▼ ] │
├────────────────────────────────────────────────────────────────────────────────────────┤
│ [✓] Use Dictionary Order │
└────────────────────────────────────────────────────────────────────────────────────────┘
Key Configuration Elements
- Hierarchical Sort Priority:
- Records are sorted primarily by Field 1 (
Region). - When ties occur in Field 1, records are sorted by Field 2 (
Total_Sales). - When ties persist across Fields 1 and 2, records are sorted by Field 3 (
Customer_Name). - Use the ▲ Up and ▼ Down buttons to adjust field evaluation hierarchy.
- Records are sorted primarily by Field 1 (
- Independent Sort Direction:
- Each field can be independently configured as Ascending (lowest to highest, A to Z, oldest to newest) or Descending (highest to lowest, Z to A, newest to oldest).
- Use Dictionary Order Checkbox:
- Toggles between standard machine ASCII sorting and human-intuitive dictionary sorting.
2. ASCII Sorting vs. Dictionary Order (High-Yield Exam Topic)
Understanding the exact difference between default ASCII sorting and Dictionary Order is one of the most frequently tested concepts on the Core Certification exam.
┌────────────────────────────────────────┐
│ Incoming Unsorted Strings │
│ ["banana", "Apple", "apple", "10"] │
└───────────┬────────────────┬───────────┘
│ │
Default ASCII Sort │ │ Dictionary Order Checked [✓]
(Byte-by-Byte Value) │ │ (Case-Insensitive Grouping)
▼ ▼
┌─────────────────┐ ┌─────────────────┐
│ 1. "10" │ │ 1. "10" │
│ 2. "Apple" │ │ 2. "apple" │
│ 3. "apple" │ │ 3. "Apple" │
│ 4. "banana" │ │ 4. "banana" │
└─────────────────┘ └─────────────────┘
1. Default ASCII Sorting (Byte-by-Byte Order)
When Use Dictionary Order is unchecked (default), Alteryx sorts strings based on their raw ASCII binary byte values:
- ASCII Hierarchy:
Control Characters→Punctuation/Symbols→Numbers (0–9)→Uppercase Letters (A–Z)→Lowercase Letters (a–z). - Key Characteristic: ALL uppercase letters come before ALL lowercase letters.
- Example Sort Result:
"10"→"Apple"→"Banana"→"apple"→"banana".
2. Dictionary Order (Lexical Grouping)
When Use Dictionary Order is checked [✓], Alteryx sorts strings in the order they appear in a standard dictionary:
- Letters are grouped alphabetically regardless of case (
aandAsort together beforebandB). - Example Sort Result:
"10"→"apple"→"Apple"→"banana"→"Banana".
ASCII vs. Dictionary Comparison Table
| Incoming Strings | Default ASCII Sort (Ascending) | Dictionary Order Sort (Ascending) |
|---|---|---|
"zebra", "Alpha", "beta", "apple" | "Alpha", "apple", "beta", "zebra" | "Alpha", "apple", "beta", "zebra" (or apple, Alpha) |
"B", "a", "A", "b" | "A", "B", "a", "b" | "a", "A", "b", "B" |
"100", "20", "3" (as Strings!) | "100", "20", "3" | "100", "20", "3" |
Exam Trap: Sorting numbers stored in string data types (e.g.,
V_String) evaluates character-by-character from left to right. Therefore,"100"sorts before"20"because'1'comes before'2'in ASCII. To sort numbers numerically ($3 < 20 < 100$), ensure the column is converted to a numeric type (Int32,Double,FixedDecimal) before the Sort tool.
3. Sorting Null Values and Empty Strings
How Alteryx handles [Null] values and empty text strings ("") during sorting is deterministic and strictly tested.
ASCENDING SORT ORDER (Lowest to Highest) DESCENDING SORT ORDER (Highest to Lowest)
┌─────────────────────────────────────────┐ ┌─────────────────────────────────────────┐
│ 1. [Null] (Very Top/First) │ │ 1. Data Values (Z to A / 999 to 0) │
│ 2. "" (Empty String) │ │ 2. "" (Empty String) │
│ 3. Whitespace / Special Characters │ │ 3. [Null] (Very Bottom/Last)│
│ 4. Numeric Values (0 to 999) │ └─────────────────────────────────────────┘
│ 5. Alphabetic Data (A to Z) │
└─────────────────────────────────────────┘
Null Sorting Rules
- Ascending Sort:
[Null]values ALWAYS sort to the very top (first records). - Descending Sort:
[Null]values ALWAYS sort to the very bottom (last records).
Empty Strings ("") vs. Null Values
- An empty string
""(a string with 0 characters) is not Null. - In an Ascending sort,
""appears immediately after[Null]and before characters. - In a Descending sort,
""appears immediately before[Null]at the bottom.
4. Impact of Sorting on Downstream Tools
Because Alteryx processes records in stream order, sorting directly changes the output of several core preparation and transformation tools:
1. Unique Tool
- The Unique tool retains the first record encountered for a given key and sends all subsequent duplicates to the
D(Duplicate) anchor. - Pattern: To keep the most recent transaction per customer, sort by
[Customer_ID](Ascending) and[Transaction_Date](Descending) before passing records into the Unique tool.
2. Sample Tool
- The Sample tool's First N rows and Last N rows modes rely completely on the upstream record sequence.
- Pattern: To identify the "Top 5 Sales Representatives", sort by
[Sales_Amount](Descending) followed by a Sample tool configured toFirst 5 rows.
3. Multi-Row Formula Tool
- The Multi-Row Formula tool evaluates expressions across adjacent rows (
[Row-1:Field],[Row+1:Field]). - Without strict upstream sorting, running totals, period-over-period differences, and lag/lead calculations produce corrupted results.
4. Find Replace & Join Tools
- Pre-sorting datasets on join keys optimizes engine memory caching and speeds up data blending operations on large datasets.
A column of data type V_String contains the following records: 'cat', 'Dog', 'apple', 'Banana'. If this column is sorted in Ascending order with 'Use Dictionary Order' UNCHECKED, what is the resulting order?
A dataset contains sales amounts including several [Null] values. If the dataset is sorted by Sales_Amount in Descending order, where do the [Null] values appear in the Results window?
A column of data type V_String contains string numbers: '10', '2', '100', '25'. If this column is sorted in Ascending order, what is the resulting sequence?
An analyst wants to deduplicate customer records using the Unique tool on Customer_ID, but wants to ensure that the record with the most recent Purchase_Date is retained in the Unique (U) output. What tool configuration is required before the Unique tool?