Filters

Filters enable to extract data from a table with data value criteria on columns.

DOT Extract has several solutions to filter the data:

Create your own selection filter on based on the combination of binary clause, unary and logical clauses,
Selection can be expressed in the form of a where clause.
Create a SQL join filter.

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.

Important!

All fields are 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 list only shows the tables in the chosen data source, and the schema relative to each table.

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. The next step for the filters to be completely set and work properly, is to define the scope of the filter.

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

All existing fields set during the creation of the filter are editable, including the name, data source and table. The edit screen is also used to define the scope of the filter, whether to Define a filter with selection clauses or to Define an SQL join filter.

Click the View icon to open the filter to edit, then click the Edit icon. The editor screen lets you edit any of the fields defined in the wizard and create the query formula to filter your data.

Warning!

Editing the Data Source and Tables fields to replace the existing ones with another removes the previously created selection and joins queries defined for a specific filter. If you change your data source or table after defining the query, you will lose all the configuration you have made so far.

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.

Define a filter with selection clauses

Define the query formula to filter your data using the selectors form the edit screen. These selectors are related to the data source and table selected when creating the filter.

Warning!

Follow the subsequent steps to define a filter with selection clauses.

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

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.

Step 3 Once you have added all the required entries for your filter, click the Save filter button.

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 the parameters fields.

However you can insert parameters directly in the WHERE clause using the following syntax: a.[Column_Name1] = ${Parameter_Name1} AND a.[Column_Name2]= '${Parameter_Name2}'.

Note

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

Define an SQL join filter

JOIN filters enable you to extract data values from one table that are present in another table. The columns of values to match are set by the chosen link or relation between the tables.

A JOIN filter can be associated with a filter which can be a predefined filter or user filter. The optional filter must be defined with tables involved in the JOIN filter.

Define the query formula to filter your data using the join selectors from the edit screen. These selectors are related to the data source and table selected when creating the filter.

Warning!

Follow the subsequent steps to define a filter with join clauses.

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

Step 2 Click the Join button to switch to the join query form.

Step 3 Select a referenced table to create the link and save.

If required, you can associate a selection clause filter directly to the Join query, from the Filter drop-down list.

Important!

Make sure that you click the Save icon every time you add a new table, 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.

Step 4 Once you have added all the required entries for your filter, click the Save filter button.

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

Variables for filters

The use of variables in filters allows you to define a particular value for the filter when executing an extraction project. Add variable tags to the selection when you create the filter and you will be prompted to define the value of the variable when the extraction is executed.

Variable tags can be added to the Value field of a binary selection criteria and the Parameter field of an unary selection criteria. For more information on how to define filter with unary and binary clauses, refer to Define a filter with selection clauses.

Add a variable tag using the following syntax: ${PARAMETER_NAME}.

After saving the filter, the tags are used to create the execution parameters of the extraction. When the execution of an extraction project is launched, the execution parameters of all the involved filters are retrieved in the process, and an intermediate page is displayed to allow you to define the value of each execution parameter.

Reference

Refer to Extractions.

List of operators corresponding to operands

Note

All the filter clauses operators are available in their Negative form

Important!

For date values in filters, click the Calendar button to select a date in the displayed calendar. You can also use variables for date filters.
If you wish to set the date manually, 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