Overview
This type of transformation merges two sets of data based on the configured Join Fields.
This is done by using the Merge step in the Data Transformation module. Note: This is a built-in step, and therefore will be available in the Transformations List by default.
Types of Join
The table below explains the types of Joins that can be applied using this step.
Join Type | Explanation | Diagram |
|---|---|---|
Inner Join | Returns records that have matching values in both tables. |  |
Left Outer Join | Return all records from the left table, and the matched records from the right table. |  |
Right Outer Join | Return all records from the right table, and the matched records from the left table. |  |
Step Configuration
Follow the instructions below to configure a merge step:
- Ensure that your flow already has two different steps that need to be merged. Once you are ready to include the Merge step in your flow, follow the below instructions:
- Expand the Transformation Steps button on the left side of the Transformation Flow builder, to view a list of transformation steps.
- Drag the Merge step from this list.
- Connect the two input steps with the merge step one by one.
- Configure the merge step details in the step configuration panel:
Select a join type. (See the chart above for a description of each of the Join types.)
In case no join type is selected, the system will invoke a Cross Join. This joins each row on the left with every row on the right, and hence isn't an ideal option. However, it can be applied to generate test data.
- Join each field to its counterpart to merge them together.
- Click on the Add Join button to include a new join field in the panel.
- Repeat these steps for any fields that are to be joined.
- You can delete a join field by clicking on its delete icon if required.
- Once done, click Apply. The values will appear in the data preview panel.
The system will colour-code fields from the different steps to help differentiate between them.
Complete Example
The following example shows a full transformation flow that involves a merge step. Our example will merge data extracted from a database table and a report (using two separate input steps), then combine them by using the merge step, and finally store the result into a database. You could always include additional steps in your own transformation flow.
Click on the Create button in the top-right corner.
Then select Transformation Flow.
If you do not see this option, you may not have security access to transformation flows. Learn how to get access here.
You will be taken to the transformation flow builder.
Hover your cursor over the input steps button on the left side. A panel with a list of all data extraction steps will appear.
Drag one of these steps onto the canvas. For this procedure, we will first use the single table step as an example. (Click here to learn about all the different input steps.)
On doing so, a popup will appear to load data from a data source.
Click on the data source that you require.
Then choose the database table, and click on Submit.
The selected table's fields will appear in the transformation flow panel to be configured.
Select only the fields that you want data to be extracted from.
You can make further changes to the step, such as renaming it, adding a description, etc.
Once you’re done with the step configuration, click on the Apply button.
You must click on the Apply button to save the configuration details. Otherwise, if you click somewhere else first, you will lose the configuration details.
On doing so, the data preview panel will display the data extracted from the configured database table.
Now follow the below steps to add another input step to import and configure another data source, for example data from a report.
- Drag the report step from the input step panel.
- Then select a report by providing its name in the popup window.
When the report's fields appear in the next popup, click Add Report.
Click on Apply when configuring the report fields in the transformation flow panel.
The data preview panel will then display the configured report's data.
When you want to include the merge step in your flow, continue with the procedure below.
Expand the transformation steps panel by hovering on its icon in the step builder, and then drag the Merge step onto the canvas.
- Connect the database input step to the merge step.
- Similarly, create a connection between the report input step and the merge step.
- Next click on the merge step to configure it. The tranformation flow panel will be updated accordingly.
Select a join type.
Join Type
Explanation
Diagram
Inner Join
Returns records that have matching values in both tables.
Left Outer Join
Return all records from the left table, and the matched records from the right table.
Right Outer Join
Return all records from the right table, and the matched records from the left table.
Note: If no join type is selected, the system will invoke a Cross Join. This joins each row on the left with every row on the right, and hence isn't an ideal option. However, it can be applied to generate test data.
Then map the left field (from the database input step) to its counterpart on the right side (from the report input step).
- To add more joins, click on the Add Join button, and repeat the mapping steps.
- You can even delete a join field by clicking on its delete icon if required.
- Once the joins have been added, click on Apply.
The data preview panel will display the result of the merge step. You will notice the fields are colour-coded according to their input steps to help differentiate between them.
You can continue to transform your data further by adding more transformation steps.
Once you are ready to save your data into a writeable database, follow the steps below.
Extend the output steps panel by hovering on its icon, and drag the SQL database output step onto the canvas.
Connect the merge step (or the previous step in your flow) to the output step by creating a connection.
Note: By default, the output step will be highlighted as red to signify that it contains errors. This is because it has not been configured yet.
And then configure the output step through the panel on the right-side. Click here to learn more about configuring this step.
You can now execute the draft flow by clicking on the run button in the top header menu. (This does a quick execution of the data rows in the data preview panel.)
Or save the flow for a full execution. To do that, click on the Publish button.
Then provide details in the popup that appears, such as providing a proper name, and selecting rights to access the flow.
Finally, click on the Save button.