The Union transform combines data from multiple structures by mapping columns onto one another. If the structures come from the same data connector, and the data provider supports union statements, then an optimized query will be generated and sent to the server.
The Union transform requires 2 input transforms.
For example, the input could be data coming from 2 different Excel worksheets:
- Input Table 1: Sales_2013
- Input table 2: Sales_2014
In the Union configuration dialog, you must configure the following settings:
Select the first table and choose elements to be used in this transform.
You need to setup column mapping if any of the column names from Input Table 2 does not match with the column names from Input Table 1. In this sample, Region does not match with Rgn and Sales does not match with Sales Amount, so mapping should be added for both columns:
Map the columns between the 2 tables. Select Input Table 2 on the Inputs dropdown:
- Map the Rgn column:
- Map the Sales column:
- Include Duplicate Rows
Duplicate rows are included by default. If set to false, removes any duplicate rows.
The output of the Union transform consists of combined rows from the 2 inputs.
The figure below illustrates the output from the Union transform:
The figure below illustrates the output when the duplicate rows are excluded: