Union Tool
Use Union to combine 2 or more datasets on column names or positions.
In the output, each column contains the rows from each input. You can configure how the columns stack or match up in the output.
Tip
This tool has a One Tool Example. Visit Access Sample Workflows to learn how to access this and many other examples directly in Designer Cloud.
Tool Components
 |
Figure: Union tool with 2 anchors.
The Union tool has 2 anchors:
Input anchor: The input anchor connects to the data streams you want to unify. The 2 angle brackets on the input anchor indicate that it accepts multiple inputs.
Output anchor: The output anchor displays the output dataset.
Configure the Tool
Stack Columns Vertically Using: Select how to stack columns. The default setting is Automatically Configure by Column Name.
Name: Stack data by column name.
Position: Stack data by the column order in the stream.
Manual Configuration: Allows you to manually specify how to stack data. When you choose this method, the columns in each input are displayed (indicated by row #1, #2, etc.). You can Reset a table By Position or By Name.
Warning
When the mode is set to Manual Configuration, Designer Cloud assumes the configuration doesn't change between the configuration of the tool and the time the workflow is run. If anything is missing, an error occurs and the workflow stops.
Manual Configuration Fields
For the Manual Configuration method, you have to configure your Output properties.
To begin, your data streams are staggered horizontally and vertically so that the data from each input dataset are in different cells.
(Optional) In the Reset dropdown, you can begin by selecting either By Position or By Name. Select Reset to reset the columns. Use this option if you know that your data streams have some columns that match by either position or name.
Next, use the arrows to begin stacking your data. Select a cell and select the left arrow or right arrow to stack it with the data field it matches.
Properties
When Columns Differ
For the auto-configuration modes, you have to select how to handle columns that differ.
Warn (Continue Data Processing): Throws a warning in the Results Grid, but continue processing rows.
Error (Stop Data Processing): Throws an error in the Results Grid and stops processing records.
Ignore (Continue Data Processing): Ignores columns that differ and continues processing rows.
Column Output Options
Output All Columns: Output includes all columns. Null values populate empty columns.
Output Only Common Columns: Output includes only the columns that each input has in common.
Output Order
Under Output Order, check Specify to specify which input dataset's data displays first in the output. After you check that box, select and hold the reorder icon in the table and drag the connection up or down.
Warning
The Specify Output Order option can cause slower performance.
Understanding the Output
2 aspects of the Union tool output are important to understand: the column names and the order of the data.
Understanding Output Data Column Names
The column names that are used in the output dataset are pulled from the input stream with the first alphanumeric value.
By default, your input data streams are labeled #1 and #2 based on the order you connected them to the Union tool input anchor. So, if the column names differ, the output dataset uses the column names from the #1 input dataset.
If you prefer to use the column names from the #2 input dataset, change the name for the input connections. Select the input streams and enter new values. The output column names are taken from the connection with the first alphabetical/numerical value in its name.
Figure: Change input source for output column names.
Understanding Output Data Order
The default output order corresponds to the order in which you connected your input datasets to the Union tool input anchor. Go to the Output Order section to learn how to change the order of your output data.