Filters

Filters are Where clauses that define the table from which the data is extracted and other filtering clauses on the data to extract.

DOT Extract has three types of filters:

Binary Clause,
Unary Clause,
Logical Clause.

Filters are accessed and managed in the Filters view.

Manage filters

Create a filter

Follow the subsequent steps to create a new filter.

Step 1 Open the Filters view from the Filters tab, then click the Add button.

Step 2 The Add a new Filter wizard opens. Fill the fields to define the required properties for the creation of a new filter.

Note

The completion of all fields is mandatory.

Name: Enter a unique Name for the new filter. This name can be modified later.
Data Source: Select a Data Source in the drop-down list, from one of the data sources previously created.
Table: Select a Table in the drop-down list. The tables displayed in the drop-down list match the chosen data source.

Step 3 Click ADD to create the set filter, or CANCEL to stop the process and close the wizard.

Result The new filter is created and appears in the list of filters.

Note

For the filters to be completely set and work properly, you have to edit them.

To copy an existing filter, click the Duplicate icon. Edit the name of the filter to copy to avoid having duplicates in the list, then click Done. Any joins, where clauses, selection criteria or parameters created for your filter is also duplicated.

Edit a filter

Follow the subsequent steps to edit an existing filter.

Step 1 Click the View icon to open the filter you need to edit, then click the Edit icon.

Note
All existing fields set during the creation of the filter are editable.

Warning!
Editing the Data Source and Tables fields to replace the existing ones with another inevitably removes the previously created Selection and Joins for a specific filter.

Step 2 Click the Selection icon, then select a filter. The available clauses are the following:

Logical: The Logical Clause is used to define multiple filters joined by AND or OR conditions.; Note
Logical Clause filters must be immediately followed by a set Binary or Unary clause.
Binary: The Binary Clause is used when an operator is applied between a column and value operand.
Unary: The Unary Clause is used when a function operator is applied to a column or between a column and a value operand.; Note
Some operators work with only one operand, it is the case for the is Null, Max and Min operators.

Important!
Make sure that you click the

Save icon every time you add a new filter in the Selection, as it is not possible to save all filters at once nor create a new filter without saving the one in the process of creation first.

Result The new filter is edited and the modifications appear in the filter details page.

Reference
For more information about the available operators for each filter, refer to the List of operators corresponding to operands.

You can convert a Selection made on a filter into a Where clause. To do so, click the View button corresponding to the filter, then click the Edit button to modify the filter parameters. Click the Selection menu then the Turn to WHERE option.

Important!

If you turn the Selection into a Where clause, it is no longer possible to use parameters in the filter.

Note

The list of available columns is automatically updated when a Filter table is modified and saved.

Delete a filter

To delete a filter, click the View icon to open the Edit page of the filter, then click the Delete icon.

A confirmation dialog opens, click Delete to confirm, or Cancel to keep the filter.

Warning!

Deleted filters cannot be accessed or recovered.

Variabilisation of filters

The variabilisation of a filter makes it possible to define some variable values during the execution of an extraction project that runs with said filter. To do so, a dedicated tag is used on filter selection fields, to indicate that the set value is exclusively defined during the execution.

Note

The variabilisation tags are defined using the following syntax: ${PARAMETER_NAME}.

During the definition of a selection criterion in a filter, it is possible to use a variabilisation tag for the value set in the second operand field.

Execution parameters
Attribute	Description
Parameter	Sets the name of the parameter as defined by the user during the filter creation.
Description	Sets the description of the parameter. This description appears while requesting for the value.
Default value	Sets the value displayed by default while requesting the parameter value.

After saving the filter, the tags are used to create the Execution Parameters, that can also be modified later on. When a user launches the execution of an extraction project, all the Execution Parameters of all the involved filters are retrieved in the process.

Note
An intermediate page is displayed to allow the user to define the value of each execution parameter. During the Extraction Execution, the variabilisation tag linked to the Execution Parameter is replaced by the input values.

Important!
It is not possible to edit the parameter name, nor add or remove a parameter.

List of operators corresponding to operands

Note
All the filter clauses operators are available in their Negative form.

Important!

For date values in filters, the accepted formats are the following:

Date: YYYY-MM-DD,
Time: hh:mm:ss,
DateTime: YYYY-MM-DDThh:mm:ss.ssssss, where the millisecond precision can be up to 6 digits and a T separator is required between the date and the timestamp.

The details of each operator are the following:

Operators available according to filter clauses
Operator	Description	Value type
Binary
==	Retrieves values that are strictly equal. The value set in the second operand is case sensitive. Example Using the == operator on a NAME column, with the a second operand set to 'Amanda', retrieves the list of employees whose name is Amanda in an Employee table.	String.
>	Retrieves values that are greater than the one set in the second operand. Example Using the > operator on a SALARY column, with the a second operand set to 10.000, retrieves the list of employees that have a salary that is greater than 10.000 in an Employee table.	Date, string or integer.
>=	Retrieves values that are greater than or equal to the one set in the second operand. Example Using the >= operator on a SALARY column, with the a second operand set to 10.000, retrieves the list of employees that have a salary that is greater than or equal to 10.000 in an Employee table.	Date, string or integer.
Contains	Retrieves values that contain the characters set in the second operand. Example Using the Contains operator on a NAME column, with a second operand set to W, retrieves the list of names containing W in an Employee table.	String.
Starts with	Retrieves values that start with the characters set in the second operand. Example Using the Starts with operator on a NAME column, with the a second operand set to E, retrieves the list of names starting with E in an Employee table.	String.
Ends with	Retrieves values that end with the characters set in the second operand. Example Using the Ends with operator on a NAME column, with the a second operand set to N, retrieves the list of names ending with N in an Employee table.	String.
=	Retrieves values that are equal. The value set in the second operand is not case sensitive.	Date, string or integer.
In	Retrieves multiple values corresponding to the ones set in the second operand. Use this filter with a double quoted element. Example Using the IN operator on an ORDER DATE column, with the second operand set to "2005-06-17","2005-06-18", retrieves the list of orders made on 2005 17h and 18h June in an Orders table.	Date, string or integer.
Unary
is Null	Retrieves results with empty values. This operator has only one operand. Example Using the is Null operator on an EMAIL column retrieves the employees that do not have an email address in an Employee table.
Max	Retrieves the column that has the maximum value. This operator has only one operand. Example Using the Max operator on a SALARY column retrieves a list of the employees that have the highest salary in an Employee table.
Min	Retrieves the column that has the minimum value. This operator has only one operand. Example Using the Min operator on a SALARY column retrieves a list of employees that have the lowest salary in an Employee table.
First Asc	Retrieves a specified number of results in the first ascendant results. The amount of results wanted is set in the second operand. Example Using the First Asc operator on a ZIP_CODE column, with a second operand set to 10, retrieves the first 10 zip codes contained in an Address table, in ascending order.	Date, string or integer.
Last Asc	Retrieves a specified number of results in the last ascendant results. The amount of results wanted is set in the second operand. Example Using the Last Asc operator on a COUNTRY column, with a second operand set to 10, retrieves the last 10 countries contained in an Address table, in ascending order.	Date, string or integer.
Longer	Retrieves results that contain more characters than the number set in the second operand. Example Using the Longer operator on a CITY column, with a second operand set to 10, retrieves the city names that contain more than 10 characters in an Address table.	String.
First Desc	Retrieves a specified number of results in the first descendant results. The amount of results wanted is set in the second operand. Example Using the First Desc operator on a ZIP_CODE column, with a second operand set to 10, retrieves the first 10 zip codes contained in an Address table, in descending order.	Date, string or integer.
Last Desc	Retrieves a specified number of results in the last descendant results. The amount of results wanted is set in the second operand. Example Using the Last Desc operator on a ZIP_CODE column, with a second operand set to 10, retrieves the last 10 zip codes contained in an Address table, in descending order.	Date, string or integer.

Submit Search

go back to top