# Union Transformation

The *Union* Transformation object in Astera is used to combine incoming data from two or more inputs into a single output. Its functionality is 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.

{% hint style="info" %}
**Note:** An input node in a union transformation cannot receive data from two different set transformations.
{% endhint %}

### Video

{% embed url="<https://www.youtube.com/watch?t=11s&v=pQd2UQuphgo>" %}

### Use-Case

In this example, we have a customers data from two different departments: Sales and Marketing, stored in two separate [*Excel Workbook Source*](https://documentation.astera.com/astera-data-stack-v10/dataflows/sources/excel-workbook-source) files. We want to combine this data into a single dataset using a *Union* Transformation object. 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 object and pass the value using a *Variables* object.

### How to Work with Union Transformation

1. To work with a *Union* Transformation object, drag-and-drop the *Union* Transformation object from *Toolbox > Transformations > Union* onto the dataflow designer.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FDnCdd5NSkzF1X5z1xolp%2Funion-transformation.gif?alt=media\&token=dfd0af02-c722-4dda-b98a-9a760fbb6e0c)

2. Map the *Customers\_Marketing* data to *Input\_1* and *Customers\_Sales* data to *Input\_2* in the *Union* Transformation object.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FsukLbPrKBfubzlQNgTNA%2F1-1590643874110.png?alt=media\&token=a5285bb9-bc17-4ae3-a9cf-906891ace68d)

3. Now, right-click on the *Union* Transformation object’s header and select *Properties*.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FN0QuPdSwRYPK5Q0yW7SF%2F2-1590643980791.png?alt=media\&token=dfec12ac-8149-4a1b-a049-c50a0ac0c9e4)

4. The first window is the *Layout Builder* window, 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, name it *Category* and specify its Data Type as *String*.

Click *Next*.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2F0GDVfjGBifpUxHjkRUFk%2F3-1590644467320.png?alt=media\&token=1f033fc6-6446-4ef5-a755-8fe856097f54)

5. Next is the *Union Transformation Properties* window 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 object.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FqBu9djIycGc7OVrB3zrd%2F4.png?alt=media\&token=0bba9879-3643-4241-97ca-bb60c401e9a7)

Click *OK*.

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

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FA7zQSq1JdltBHE3GgMq5%2F5-1590644619980.png?alt=media\&token=cd13d63b-ae6e-41c8-9bbe-e0d1236a83e5)

Now, we have successfully configured the *Union* Transformation object.

7. Right-click on the *Union* Transformation object’s header and select *Preview Output*.

<figure><img src="https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FhVtOMcJRMaMtVyJP8MQV%2F6-1590644812403.png?alt=media&#x26;token=cb0ab224-87b6-48e4-8eed-58581394e7b4" alt=""><figcaption></figcaption></figure>

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

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2Fyb3ozC1tbtveJiqy5MAj%2F7-1590644977738.png?alt=media\&token=3e4cb6d6-705c-499d-9734-9aa8ff59cfa6)

{% hint style="info" %}
**Note:** A *Union* Transformation will show the combined fields from both sources as a resultant, regardless of whether they are present in one or both datasets. In the final output, the dataset which does not have one or more fields will be assigned a null value in that field.
{% endhint %}

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

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FbTKL68dl729XQBDnPt8K%2F8-1590646862122.png?alt=media\&token=eb6aff91-a2ca-4c0b-a68e-0a8e6fa406f7)

This concludes working with the *Union* Transformation object in Astera.