5.2 Union Tool & Output Configurations

5.2 Union Tool & Output Configurations

Core Certification Focus: The Union tool combines multiple data streams vertically (stacking rows). Exam candidates must know its three configuration modes (Auto Config by Name, Auto Config by Position, and Manually Configure Fields), how unmatched columns and case differences are handled, how to enforce record output order using "Set a Specific Output Order", and how Alteryx Union differs fundamentally from SQL UNION / UNION ALL.

---

1. Union Tool Architecture & Canvas Role

The Union tool (located in the purple Join category) appends multiple incoming data streams with similar or identical structures into a single unified table.

Tool Anchor Layout

• Input Anchors (Multiple Connections to 1 Physical Arrow): Accepts two or more input streams simultaneously. Each connection is automatically assigned an input number (#1, #2, #3, etc.).
• Output Anchors (1): Outputs a single consolidated dataset.

+-----------------------------------------------------------------------------+
|                        UNION TOOL ANCHOR ARCHITECTURE                       |
|                                                                             |
|   [ Stream #1: Jan Data ] ---> (#1)                                         |
|   [ Stream #2: Feb Data ] ---> (#2) ===> [  UNION  ] ---> (1 Output Anchor) |
|   [ Stream #3: Mar Data ] ---> (#3)      (Purple)                           |
+-----------------------------------------------------------------------------+

---

2. The Three Configuration Modes

The Union tool provides three distinct modes for aligning columns across incoming data streams:

+-----------------------------------------------------------------------------+
|                         UNION CONFIGURATION MODES                           |
+-----------------------------------------------------------------------------+
| (o) Auto Config by Name                                                     |
| ( ) Auto Config by Position                                                 |
| ( ) Manually Configure Fields                                               |
| --------------------------------------------------------------------------- |
| When Properties Differ:                                                     |
|   [ Output All Fields                     v ]                               |
|   [X] Set a Specific Output Order                                           |
+-----------------------------------------------------------------------------+

Mode 1: Auto Config by Name (Default)

• Mechanics: Aligns fields across incoming streams based on exact field name matching, regardless of their column order or position.
• Unmatched Fields: If Stream #1 contains [ID], [Name], [Discount] and Stream #2 contains [ID], [Name], [Tax]:
  • The output contains all 4 fields: [ID], [Name], [Discount], [Tax].
  • Stream #1 rows have [Tax] populated with [Null].
  • Stream #2 rows have [Discount] populated with [Null].
• Case Sensitivity Trap: Field names are case-sensitive. If Stream #1 has Sales and Stream #2 has sales, Alteryx treats them as two distinct columns, outputting both Sales and sales with alternating Null values.

Mode 2: Auto Config by Position

• Mechanics: Aligns fields strictly by their physical column index order (1st column of Stream #1 matches 1st column of Stream #2; 2nd matches 2nd, etc.), completely ignoring field names.
• Field Naming Rule: The output data stream inherits the field names of the first input stream (#1).
• Column Count Discrepancies: If Stream #1 has 3 columns and Stream #2 has 5 columns:
  • Columns 1–3 are aligned by index.
  • Columns 4–5 adopt the field names from Stream #2 and are filled with [Null] for records originating from Stream #1.

Mode 3: Manually Configure Fields

• Mechanics: Provides an interactive visual layout grid where fields from each stream appear in separate columns.
• Custom Alignment: Developers can manually drag, shift, and align mismatched column headers (e.g., aligning Cust_ID from #1 with AccountNum from #2 and Client_No from #3 into a single unified column).
• Control Buttons: Includes buttons to Reset, move fields Up/Down/Left/Right, and Delete unneeded mappings.

Summary Comparison of Union Modes

---

3. Output Record Sequencing: "Set a Specific Output Order"

By default, Alteryx Designer processes and outputs records from the Union tool in an indeterminate or arrival-time order based on upstream execution speed and data streaming engines.

+-----------------------------------------------------------------------------+
|                        OUTPUT ORDER CONFIGURATION                           |
+-----------------------------------------------------------------------------+
| [X] Set a Specific Output Order                                             |
|                                                                             |
|     Order  | Connection Name                                                |
|     1      | #1 - Jan_Transactions.csv                                      |
|     2      | #3 - Feb_Transactions.csv                          [ Up ]      |
|     3      | #2 - Mar_Transactions.csv                          [ Down ]    |
+-----------------------------------------------------------------------------+

Configuring Output Sequence

1. Check the box for "Set a Specific Output Order" in the Union configuration pane.
2. Select an incoming stream in the priority list.
3. Click the Up or Down buttons to dictate the exact top-to-bottom record stacking order (e.g., ensuring January records are placed first, followed by February, then March).

Exam Tip: If "Set a Specific Output Order" is disabled (unchecked), Alteryx does not guarantee that Stream #1 records will appear before Stream #2 records in the output grid.

---

4. Data Type Coercion & Schema Mismatch Warnings

When combining streams where matching fields possess differing data types, Alteryx handles the discrepancy through automatic type coercion and logging:

• String vs. Numeric: If Stream #1 has Amount as Double and Stream #2 has Amount as V_String, Alteryx coerces the combined output field to a String (V_WString or V_String) to prevent data loss.
• String Size Differences: If Stream #1 has Region as String (10) and Stream #2 has Region as V_String (50), the output field expands to the larger size (V_String (50)).
• Results Window Diagnostics: When type coercion occurs, Alteryx executes the workflow successfully but generates a Conversion Warning in the Results window log (e.g., "The field 'Amount' was forced to a string type due to conflicting types across input streams").

---

5. Alteryx Union vs. SQL UNION Mechanics

A critical conceptual distinction tested on the exam is how Alteryx Union relates to relational database operations:

SQL UNION:        Combines datasets AND removes duplicate rows (Equivalent to UNION DISTINCT)
SQL UNION ALL:    Combines datasets and KEEPS all duplicate rows
ALTERYX UNION:    Stacks all incoming rows without deduplication (Equivalent to SQL UNION ALL)

To achieve the equivalent of a SQL UNION (deduplicated), you must attach a Unique tool immediately downstream of the Union tool, selecting all fields to identify and purge duplicate records.

---

6. High-Yield Exam Traps & Best Practices

[!WARNING] Exam Trap: Case Sensitivity Creates Extra Columns In "Auto Config by Name", column headers Sales, sales, and SALES will generate three separate output columns, each filled with Null values for non-matching rows. Always standardize column headers with a Select tool before unioning.

[!IMPORTANT] Exam Trap: Position Mode Column Names When using "Auto Config by Position", the column names of the output dataset are always inherited from the first incoming stream (#1). If Stream #1 has headers [A, B, C] and Stream #2 has headers [Date, Region, Sales], the output headers will be [A, B, C].

[!NOTE] Exam Trap: Input Limit in Union Tool Unlike the Join tool (which strictly accepts 2 inputs: L and R), the Union tool can accept an arbitrary number of incoming connections (#1, #2, #3, #4, ... #N) into its single input anchor.