Like what you see? Have a play with our trial version.

 

Overview

The filter step is used to filter out your data within a transformation flow. Certain input steps (such as, single table and third-party connectors) also allow for filtering when being configured, but with the help of this step, it is possible to filter data any time in the flow.

This is done by using the Filter step in the Data Transformation module. Note: This is a built-in step, and therefore will be available in the Transformations List by default.

 

 

Understanding the Filter Screen

When configuring this step, you will see a Filter popup that allows you to create a filter. Following is an understanding of this popup .

 

No.

Filter Setting

Description

1

AND/OR Logic

Define the logic used between each filter condition.

2

Bracket Arrows

The addition of brackets around sets of filters allows for more complex logic, used in conjunction with AND/OR logic settings.

3

Filter Fields

The fields added to the Filters list in order to restrict the report results.

4

Operator Selection

Select the operator to be used in the filter, specifying how values will need to match, or differ from the condition defined.

5

Value Selection

Define a value for the filter condition.

6

Add Filters

Allows the user to add more fields to the filters list without closing the configuration panel.

7

Filter Logic

Displays a summary of the filters.

 

Step Configuration

Follow the instructions below to configure a filter step:

  1. Ensure that you have at least one input step containing the data that you want to transform. Once you are ready to include the Filter step in your flow, follow the below instructions: 
  2. Expand the Transformation Steps button on the left side of the Transformation Flow builder, to view a list of transformation steps.
  3. Drag the Filter step from this list onto the canvas.



  4. Connect this step to the previous step in the flow.
  5. Next configure the step to apply filtering to the data through the configure panel.



  6. Click on the Add Filters option.
  7. Use the new popup to add filters.



    1. Select a field.
    2. Then choose a filtering operator. 
    3. Click Define Value to set a manually define a value to filter the field data by.
    4. You can perform these steps again to add more filters.
    5. Use the And/Or field to define logic between each filter condition.
    6. Click on Submit. The transformation flow's data will become filtered.



  8. You can also remove all the filters using the Clear option or edit them by clicking on the Edit Filters option.

 

 

Complete Example

The following example shows a full transformation flow that involves a filter step. This transformation will involve setting up an input step, creating a custom calculated field, filtering the data, and then storing it into a database. You could always include more steps to your transformation flow. 




  1. Click on the Create button in the top-right corner.

  2. 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.





  3. You will be taken to the transformation flow builder.

  4. Hover your cursor over the input steps button on the left side. A panel with a list of all data extraction steps will appear.



  5. 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.)

  6. On doing so, a popup will appear to load data from a data source.



  7. Click on the data source that you require.

  8. Then choose the database table, and click on Submit. 



  9. The selected table's fields will appear in the transformation flow panel to be configured. 

  10. Select only the fields that you want data to be extracted from.



  11. You can make further changes, such as renaming the step, including a description, etc.

  12. Once you’re done with the input step configuration, click on the Apply button. 

  13. On doing so, the data preview panel will display the data extracted from the configured database table.  



  14. In our example, we will now create a calculated field based on our data.

  15. Drag the calculated field step and create a connection with the input step.



  16. Then click on the calculated field step icon to configure it. The transformation flow panel wil be updated.

  17. Click on Add Item.



  18. A popup window will appear to create a new calculated field.



  19. Update the name of the calculated field.
  20. Define your calculation using the buttons at the bottom of the window, for example:
    • Search for the Invoiced Amount field in the -- Select Field -- drop down list and click on it to add it to the formula box.
    • Press the divide ( / ) button.
    • Search for the Cost field and add it to the formula.
    • You will now have Invoiced Amount / Cost.
    • Test your calculation by using the Validate button. (An error will appear if it is invalid.)
  21. If the calculation is valid, click Save. 



  22. The data preview panel will display a new column field for this formula's result.



  23. Now include the filter step in your flow by following the steps below.

  24. Expand the transformation steps panel by hovering on its icon in the step builder, and then drag the Filter step onto the canvas. 



  25. Connect this step to the previous step in the flow.



  26. Next configure the step to apply filtering to the data through the configure panel.
  27. Click on the Add Filters option.
  28. Add filters using the settings on the new popup.

    No.

    Filter Setting

    Description

    1

    AND/OR Logic

    Define the logic used between each filter condition.

    2

    Bracket Arrows

    The addition of brackets around sets of filters allows for more complex logic, used in conjunction with AND/OR logic settings.

    3

    Filter Fields

    The fields added to the Filters list in order to restrict the report results.

    4

    Operator Selection

    Select the operator to be used in the filter, specifying how values will need to match, or differ from the condition defined.

    5

    Value Selection

    Define a value for the filter condition.

    6

    Add Filters

    Allows the user to add more fields to the filters list without closing the configuration panel.

    7

    Filter Logic

    Displays a summary of the filters.



    1. Select a field.
    2. Then choose a filtering operator. 
    3. Click Define Value to manually set a value to filter the field data by.
    4. You can perform these steps again to add more filters.
    5. Use the And/Or field to define logic between each filter condition.
  29. For our example, we will filter the data to display only values of the previous calculated field step, that are greater than 2.



  30. Click Submit. The transformation flow's data will become filtered, as seen in the data preview panel.



  31. The configure panel on the right will also display the filter that was created.



  32. You can remove a filter using the Clear option, or edit it by clicking on the Edit Filters option.

  33. Once you are ready to save your data into a writeable database, follow the steps below.

  34. Extend the output steps panel by hovering on its icon, and drag the SQL database output step onto the canvas.



  35. Connect the filter 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.



  36. And then configure the output step through the panel on the right-side. Click here to learn more about configuring this step.

  37. 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.)


  38. Or save the flow for a full execution. To do that, click on the Publish button.




  39. Then provide details in the popup that appears, such as providing a proper name, and selecting rights to access the flow.




  40. Finally, click on the Save button.

 


 

 




 

  • No labels