Filtering can be applied to a query based on some filtering criteria. This transformation technique can be used to extract specific data from the query. The DataEngine library lets you define a filter query to select a subset of rows or columns from a base table/query result table. It also lets you restrict the query calculation to a subset of the table rows using a filter condition.

There are two different kinds of filter conditions, which can be defined in a query,_filter and _range. The _filter criteria can represent any filter condition, but makes the DataEngine scan the entire set of the table rows and check the condition on each row. Therefore, the _filter condition comes at a performance cost. On the other hand, with the ‘_range’ criteria, the DataEngine need not scan all the rows and hence can perform faster.

The different filtering conditions which can be applied to the table columns include Gte, Lte (greater than or equals, less than or equals), Gt, Lt (greater than, less than), etc. and can either be applied individually or in combination.

Defining a query to perform filter operations

This section describes how we can formulate a DataEngine query to perform the filter operation on the base tables. These conditions are implemented as methods in the ColumnCondition class of the C1.DataEngine assembly, which are invoked while defining the query used to perform the filter operation.

The section below elaborates on how to use the _range and _filter attribute while formulating queries:

Let's say a user has imported data from “Invoices” database table to the DataEngine base table named “Invoices” (Refer Connect DataEngine to Database topic for more details). If the user wishes to find out the country-wise sales of a specific product say ’Ipoh Coffee’, then it can be done by defining the following query using the _range filter condition as shown in the code snippet below:

dynamic invoice = workspace.table("Invoices");

// Find country-wise sales of a specific product
dynamic query = workspace.query("ProductSales", new
{
    _range = invoice.ProductName.Eq("Ipoh Coffee"),
    invoice.ProductName,
    invoice.Country,
    Sales = Op.Sum(invoice.ExtendedPrice)
});

The above query selects all the rows with the ProductName ‘Ipoh Coffee’. This process is very efficient as the query traverses to the desired segment of rows using an index built internally by DataEngine and outputs just those rows with ProductName ‘Ipoh Coffee’ without checking any other rows. The above query uses Eq that is "equals" operation. You can even combine the conditions applied to the columns as:

_range = invoice.Quantity.Gte(90).Lte(100),

_range = invoice.Discount.Eq(0)+invoice.UnitPrice.Gte(2).Lte(5),

Some conditions cannot be specified as _range. For example, A "not equal" or Ne condition cannot be used in a range. So, to use Ne condition, you need to apply _filter attributeas illustrated below.

// Retrieve the base table for use in constructing queries
dynamic invoice = workspace.table("Invoices");

// Find country-wise sales of all products except 'Ipoh Coffee'
dynamic query = workspace.query("ProductSales", new
{
    _filter = invoice.ProductName.Ne("Ipoh Coffee"),
    invoice.ProductName,
    invoice.Country,
    Sales = Op.Sum(invoice.ExtendedPrice)
});

DataEngine filtering transformation can be implemented using the Or method as well as And method of the ColumnCondition class. Note that Or cannot be used with the _range criteria, and can only be used with _filter criteria as shown in the code below:

The And method can be used in conjunction with Or method in a single filter. For example, to filter the price values that are either less than 10 or between 20 and 30, the Or and And methods can be used together. This is shown in the code snippet below:

Similarly, you can have any number of Or and And connectives, and impose conditions on multiple columns combining them with the ‘+’ operator as shown in the code snippet below: