# Tree Join Transformation

The *Tree Join* Transformation object in Astera enables users to create complex, hierarchical data structures such as EDI or XML documents with ease. Unlike the standard relational join which combines left and right elements to create a new record, the *Tree Join* Transformation object allows users to create collection and member nodes. It also enables users to join datasets in parent-child hierarchies using a key field. It is a set level transformation that operates on a group of records.

In this document, we will learn to use the *Tree Join* Transformation object in Astera.

### Video

{% embed url="<https://www.youtube.com/watch?v=akQVzwSl85g>" %}

### Use Case

In this use case we have two different source datasets. The first data contains information about *Customers* and has fields such as *CustomerName*, *CustomerID*, *Address*, etc.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/5MBSMHoGjX71ZQ3jkojo/01-Customer-Dataset.png)

The second dataset contains details of *Orders* placed by customers. It includes fields such as *OrderID*, *CustomerID*, and order details such as *RequiredDate*, *ShippedDate* and other shipping details.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/oYQ2OEXNmoHUDjqwCpCn/02-Orders-Dataset.png)

We will join these two datasets using the *Tree Join* Transformation object and create a hierarchical dataset in which all orders placed by a customer along with the order details are represented in a parent-child hierarchy.

Contrary to the regular Join that joins two datasets in a flat layout, the *Tree Join* Transformation joins data from two different datasets into a hierarchical data structure.

In this use case, each record from the first dataset that contains *Customer* details will be a parent node, and under the parent node, the details of *Orders* placed by that customer will be returned in a child node.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/Yu7VSfkfKm9eM1kbbcC5/03-TreeJoin-Transformation.png)

### Using the Tree Join Transformation

1\. To get the *Tree Join* Transformation object from the Toolbox, go to *Toolbox > Transformations > Tree Join* and drag-and-drop the *Tree Join* Transformation object onto the designer.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/0s2eflHQ4quetqyCsFcd/04-Getting-TreeJoin-Transformation.gif)

2\. Now map fields from the *Customer* source dataset to the *TreeJoin* object.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/QQev57qxr3l7zzlSvyDo/05-Maping-Customers-to-TreeJoin.gif)

3\. Right-click on the *Tree Join* object header and go to *Properties* from the context menu. In the *Tree Join* *Layout Builder* window, you can see the fields from the *Customer* dataset listed under the root node.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/7utpfCXEDW5aUCy5HTXp/06-Customer-Fields-in-TreeJoin-Properties.png)

4\. Next, click on the *TreeJoin* node, you will see that the small icons or buttons at the top of the screen will become active. If you click on the icon, you will get two options:

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/M092ibP8eXq8rcU7w6Yb/07-Add-Object-to-TreeJoin.gif)

* *Add Member Object* – To add a new member node to your layout
* *Add a Collection Object* – To add a new collection node under the parent node. It will return all corresponding records as a collection under the parent node.

  In this case we will *Add a Member Object* to create a separate record for each order placed by a customer, under a separate customer record node.

5\. *Add a Member Object* to this root node. A dialogue box will open to name your member object.

In this case, let’s name it ‘*Orders*’ and click *OK*. A member object has been added to our parent node.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/RFohCoKIKUWStUqJE8q3/08-Adding-Collection-Object.gif)

6\. Click *OK*, to close the properties window. Now map the *Orders* dataset to the member node that we created in the previous step to complete the layout.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/e667CSJEBxAbTqOL13JU/09-Clsoing-Propeties-Window.png)

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/sS9nJVS1obEiJFv5504z/10-Mapping-Orders-Data.gif)

7\. Go to the properties of the *TreeJoin* object again. We have already created the layout, so we will proceed to the next window.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/QcIm4LlTicLY5lcHnWqn/11-Next-Properties-Window.png)

8\. In the *TreeJoin Transformation Properties* window, we must specify the Join Key.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/KhqKWmYtaiguaGPIGJy2/12-TreeJoin-Transformation-Properties.png)

The join key is a common field or a common identifier in both the datasets which will identify and join records in a tree-like structure. The parent and child fields are the same fields which are common in both the source datasets and serves as a key identifier to join records.

* *Parent Field* – Join field from the first dataset.
* *Child Field* – Same field as the parent field, selected from the second dataset.

In this case, the *CustomerID* field is common in both the datasets, so we will use it as the join Key.

9\. Click on the *Parent* *field* dropdown button. Expand the *TreeJoin* node and select the *CustomerID* field.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/P2t8a6VRxHhf2Xgr7hyX/13-Selecting-Parent-KeyField.gif)

10\. Click on the *Child* field column and expand the *TreeJoin* root node. Scroll down to your member node, expand this node and select the *CustomerID* field from the second dataset.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/08N4rlL2BAMWUavGruHr/14-Selecting-Child-KeyField.gif)

Let’s discuss the other options on the properties window:

* *Join In Database* – Lets you join the tables in the database itself rather than in-memory. However, it applies only when both the tables are sourced from the same database.
* *Case Sensitive* – To process and join records on a case sensitive basis.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/sP8Vo4XOgMOgYkbkEiuj/15-TreeJoin-Properties.png)

11\. We have our layout and the *TreeJoin* properties ready, click *OK*.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/uHy4gJ2o75wXqgPOEHEU/16-TreeJoin-Configured.png)

12\. Right-click on the *TreeJoin* object and select *Preview Output*.

![](https://docs.astera.com/projects/centerprise/en/10/_images/17-Treejoin-Output.png)

The *TreeJoin* object has returned the customer records in parent nodes. Upon expanding the node, you can see the order placed by the customer listed as its member unit under the parent node.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/ytcuzLdinjtqFOWKP86N/18-Preview-Output-TreeJoin.gif)

If we choose to *Add a Collection Object* in the *Layout Builder*, all the records for orders placed by a customer will be returned in a collection under a single parent node for each customer.

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/A86WUsrjS7LHLDOslvYx/Collection-Output.gif)

13\. The joined dataset can now be written to a desired destination. In this case we will write it to an *XML File Destination* object.

To know more about writing to an *XML File Destination* object, click [here](https://documentation.astera.com/dataflows/destinations/xml-file-destination).

![](https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/luU6PlNHjspWerPoY4Rv/19-TreeJoin-UseCase.png)

This concludes using the *Tree Join* Transformation object in Astera.

You can download the file for this use case from the following link:

{% file src="<https://content.gitbook.com/content/zEifS4h8yurLAAwiGNX2/blobs/0sRpDld679gnO8B9BP0M/Northwind.zip>" %}