This type of data transformation creates a calculated field based on other fields.
This is done by using the Calculated Field step in the Data Transformation module. Note: This is a built-in step, and therefore will be available in the Transformations List by default.
Follow the instructions below to configure a calculated field step:
You may also edit or delete a calculated field through here.
Following is an example that shows a full transformation flow involving a calculated field step. This transformation will involve setting up an input step, applying the calculated field transformation step, and then storing the data into a database. You could always include more steps to your 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 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, such as renaming the step, 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.
You can also apply transformations to fields directly from the data preview panel. Click here to learn more about this.
You can add further steps to your transformation flow.
When you want to include the calculated field 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 dragging the Calculated Field step onto the canvas.
Next, create a connection between the input step (or the previous step, if you've added any other ones) and the calculated field step, by dragging a connection point to the calculated field step.
Click on the calculated field step icon to configure it. The transformation flow panel wil be updated.
Click on Add Item.
A popup window will appear to create a new calculated field.
If the calculation is valid, click Save.
The data preview panel will display a new column field for this formula's result.
You may also edit or delete a calculated field through here, by clicking on its menu.
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 calculated field step (or the previous step in your flow) to the output step by creating a connection.
And then configure the output step through the panel on the right-side. Click here to learn more about configuring this step.
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.
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.