Filter Processor

Filter processor is a tool with which you can manipulate and group the database records to filter out those records which meet specific criteria.

In the Data Designer, the filtering can be done by using the built-in functions or using the Javascript function for more complex filtering. Multiple levels of filtering are supported, up to three filter conditions can be set for each field within a single processor.

The Filter processor is selected from the menu bar of the Designer window and then placed in the designer window workspace.

Properties

The editable properties are shown in Figure 4.28, “Filter Wizard”.

Figure 4.28. Filter Wizard

Filter Wizard

There are four tabs in the Filter Wizard: Filter#1, Filter#2, Filter#3 and JavaScript.

All the Filter tabs have the following columns:

Combining Filters

All criteria entered on a Filter tab must be true for the record to be passed through. For example, using /ElixirSamples/DataSource/FruitSales.ds as the input to the filter, setting Filter #1 to read

  • CompanyName Equals A
  • Fruit Equals Apple

will ensure that only those records where CompanyName=A AND Fruit=Apple will be passed through.

However, any records not matched by Filter #1, may still be matched by Filters #2 or #3. Within a tab the rule is AND. Across tabs, the rule is OR. So if we enter in Filter #2

  • CompanyName Equals B

we will get those records where (CompanyName=A AND Fruit=Apple) OR CompanyName=B. Therefore we will also get all records where CompanyName is B, regardless of the value of Fruit.

Table 4.1. Filter Criteria

WhenConditionRemarks
  • Equal/Not Equal

  • More Than/Not More Than

  • Less Than/Not Less Than

Type:

Comparable types: String, Numeric, Date, Time, Timestamp

Example:

10

1961-08-26

This condition uses a simple comparison of values, which must both be of comparable types. For example, strings can be compared with strings, but not with dates.
  • Matches/Not Matches

Type:

String

Example:

US|Mex

This condition allows a regular expression to be used. Use of operators like "|" for OR and "&&" for AND relationship. This example will match USA or Mexico strings.
  • In Range/Not In Range

Type:

Numeric, Date, Time, Timestamp with "~" as separator

Example:

1998-01-01~1998-12-31

  • In DataSet/Not in DataSet

Type:

Matching field type from another data source

Example:

See remarks

repository:/Sample/DataSource.ds:Field1

This retrieves the matching field from another data source for filtering values.

  • Null/Not Null

Nothing to enter

Check whether if there is no content. Note that zero value is not considered null.

  • JavaScript/Not JavaScript

Enter a script condition by specifying the matching type.

Example:

Field1==5|Field2=="US|Mex" where Field1 is numeric and Field2 is string.

This is useful for enter multiple conditions or complex conditions using standard JavaScript.

Working with Filters

  1. Add a new Composite DataSource named Filter.

  2. Add the JDBC data source for Customer and Stores by dragging and dropping them over the Composite diagram.

  3. Place the Filter processor on the diagram.

  4. Connect the Customer data source to the Filter processor and connect the output of the Filter to the Result. The designer window appears as shown in Figure 4.29, “Sample Filter Flow”.

    Figure 4.29. Sample Filter Flow

    Sample Filter Flow

Filtering records by setting the When condition in Filter tab window

To start, let's filter the records of married customers with customer_id less than 11:

  1. Open the Filter Properties and in the row corresponding to customer_id field select Less Than from the When column. Enter the condition as 11.

  2. In the Marital Status row select Equals from the When column and enter the condition as M. After setting the field properties the Wizard appears as shown in Figure 4.30, “Filter Result”.

    Figure 4.30. Filter Result

    Filter Result
  3. Click Finish button and review the Result data. Notice that only the records of married customers with customer_id less than 11 are shown.

Filtering records using the JavaScript functions

Now let's find stores in Mexico with store_id less than 10. For a change we will use JavaScript, though this can be solved just as easily with the Filter tabs.

  1. Disconnect the Customer datasource and connect the Stores datasource to the Filter input.

  2. Open the Filter Properties and remove any existing filter criteria. Then select the JavaScript tab window.

  3. Enter the following JavaScript syntax:

    store_id<10 && Country=="Mexico";
    Note that because this expression is entered as a String, you can use dynamic parameters to customize the expression at runtime.
  4. Click the Finish button and review the Result data. Notice that the records corresponding to Mexico with store_id<10 are fetched.

Note

If in the above syntax instead of "&&" the "||" symbols are specified then all the records corresponding to Mexico and all the records having store_id<10 are fetched.