Filtering takes one value as the input and produces another table with the same schema but with the same or a lower number of rows, based on filter conditions (substraction).
By default, the source is optional, meaning that the table does not need to exist. If the source table is missing, the step will not be executed and its target table will not be written. There is a way to define that the source is required. If this is used, in case of a missing table the step will fail and report an error.
Configure Filter in Raynet One Data Hub
•SOURCE TABLE: Enter the table that is used as source for the data. It supports auto-completion and will offer all available tables matching the current input string.
•FILTER CONDITIONS: It is possible to add Filter Conditions rules and rulesets either as And or as Or conditions. Select And for filter where all conditions of the filter must be met and Or for filter where one of the conditions must be met.
•OPERATION TYPE: Choose either SQL or Basic as operation type for the rule or ruleset. If SQL is chosen as type, it is possible to use custom SQL macros for the filters. Be aware that if using SQL for filtering, the expression for evaluation should return either True or False. If Basic has been chosen, there are a couple of fields available for configuration of the filter.
Basic
•COLUMN: Enter the column that will be used for the filter. It supports auto-completion and will offer all available columns matching the current input string for selection.
•OPERATOR: Select the operator to use for the filter. The following operators are available: >, ≥, <, ≤, =, ≠, Contains, Does not contain, Starts with, Does not start with, Ends with, and Does not end with.
•VALUE: Enter the value that should be used. By clicking on the button located on the left side of the field it is possible to switch the input type between text input, number input, and checkbox input.
SQL
•SQL: Enter an SQL macro containing the filter into this field. If using SQL for filtering, the expression for evaluation should return either True or False.
Detailed information on how to use scripts in ETL can be found in the Programmability chapter in the ETL Implementation Guide.