Union Transformation

The Union transformation in Astera Centerprise is used to combine incoming data from two or more inputs into a single output. It functions similar to a union operator in SQL query. It has multiple input nodes and a single output node. It puts together two sets of data irrespective of the repetition that might occur in the datasets. In order to perform this transformation on two datasets, their cardinality must be same.

Note: An input node in a union transformation cannot receive data from two different set transformations.

Use-Case

In this example, we have a customers data from two different departments: Sales and Marketing, stored in two separate Excel files. We want to combine this data into a single dataset using Union transformation. To keep track of records coming in from each department, we will also add a new field ‘Category’ in the layout of the Union transformation and pass the value using a Variable object.

How to Work with Union Transformation

1. To work with Union transformation, drag and drop the Union transformation object from Toolbox > Transformations > Union onto the dataflow designer.

2. Map the Marketing data to Input_1 and Sales data to Input_2 in the Union transformation object.

3. Now, right click on the Union transformation’s header and select Properties.

4. The first screen is the Layout Builder screen where you can customize your layout or modify your fields. You can also provide a default value to be used in place of null values.

Add a new field and name it as Category and specify its Data Type as String.

Click Next.

5. Next is the Union Transformation Properties screen where there are two input nodes defined as Input_1 and Input_2 by default. You can also rename them if you want. You can also define any number of input nodes based on the number of datasets you want to combine using the Union transformation.

Click OK.

6. Now, map the categories of respective departments from the Variable resource object to the Category field in the Union transformation object. This is done to identify which department a particular record is coming from.

Now, we have successfully configured the Union transformation object.

7. Right click on the Union transformation’s header and select Preview Output.

You can see that the Union transformation has successfully combined the two datasets into a single, unified dataset.

Note: Union transformation will show the combined fields from both the sources as a resultant, regardless of whether they are present in one or both the datasets. In the final output, the dataset which does not have one or more fields will be assigned null value in that field.

8. You can now further transform your dataset or write it to a destination.

This concludes working with Union transformation in Astera Centerprise.