Filter and Sort in Refiner

By using API calls to build up filter expressions and sort rules, you can use Refiner to return results that fit your specified criteria.

Filter expressions and sort rules can be as simple or as complex as you need them to be.

Avoid filtering or sorting on record fields that are subject to frequent updates. Frequent updates can have an adverse affect on the performance of Caplin Refiner, Liberator, and the requesting clients.

For an example of how to use filter expressions and sort rules in a client application by calling the StreamLink API, see How can I…​ Access Refiner through StreamLink.

For information about how Caplin Trader uses Refiner to sort and filter data in grids, see How Can I…​ Configure sorting and filtering (in Caplin Trader 4 grids).

Filtering rules

To filter container elements, you use API calls to build up a filter expression that’s then sent to Caplin Refiner.

A simple filter expression would be:

CpnRate>6:

  • CpnRate is the record field to be filtered

  • 6 is the value to compare against

  • > is the operator (in this case "greater than")

So this filter expression means: "Supply only those container records where the CpnRate field has a value greater than 6."

You can apply filters to both text and numeric fields.

Filter Expression Operators

Here’s the list of operators you can use in filter expressions:

Operator Meaning applies to

=

equal to

text or numeric fields (usually used for text)

!=

not equal to

text or numeric fields (usually used for text)

==

equal to (numeric)

numeric fields only

!==

not equal to (numeric)

numeric fields only

<

less than

numeric fields only

>

greater than

numeric fields only

⇐

less than or equal to

numeric fields only

>=

more than or equal to

numeric fields only

~

regular expression match

text or numeric fields (usually used for text)

#

case insensitive

regular expression match

text or numeric fields (usually used for text)

Filters can contain any number of expressions, joined with & (AND operator) or | (OR operator). For example:

MaturityDate=20130328|(CpnRate>6&CpnRate<11)

In this example, the MaturityDate field is "28 March 2013" in the format YYYYDDMM.

There’s no implicit precedence between ‘&’ and ‘|’, so you need to establish precedence in expressions by using brackets. For example, instead of writing the expression a&b|c, write (a&b)|c or a&(b|c).

Sort rules

To sort container elements, you use API calls to build up a sort rule that’s then sent to Caplin Refiner. Container sort rules consist of three parts:

  • The name of the field to sort by

  • The type of the field, which can be text, number, or a custom type. Text sorts are case-insensitive.

  • The sort sequence, either ascending or descending

Here’s an example of a sort rule:

sort=MaturityDate:number:ascending

This sort rule specifies that the filtered records are to be sorted in ascending numeric order of the MaturityDate field

You must specify the type of the field so that Refiner can correctly sort the records in the intended order . For example, assume there are three records A, B, C to be sorted on a particular field, and this field has values 10, 20, and 100 respectively. If the field type is specified as numeric, an ascending sort returns the records in numeric order, like this:

A: 10
B: 20
C: 100

But if the field type is specified as text, the records are sorted alphabetically. An ascending sort therefore returns the records in a different order:

A: 10
C: 100
B: 20

Containers can only be sorted by one field, so you can’t apply a secondary sort. In cases where two or more records have the same value in the sort field, they’re sorted relatively into the natural order as defined by the Integration Adapter that supplies the container (or part of the container). This is the order in which the Adapter would return unsorted records with this field value to the client. Consider the container /CTR/FI/ALL discussed in the Refiner Overview. Sorting this container by the Description text field results in the following ordering, where the order of the records within the same Description value is the natural order:

refinerrefiner1

Grouping

Grouping adds extra records to the container that act as headers for blocks of records with the same field value. For example, if you sort the /CTR/FI/All container by the Description field and group the records by the Description field, the returned container looks like this:

Container: /FILTER/FI/ALL?sort=Description:text:ascending;groupby=Description

/FILTER/META/CTR/FI/ALL/GROUPHEADER/groupby=Description/AUSTRIA
SubHeaderText=AUSTRIA
/FI/EU/A Description=AUSTRIA CpnRate=4.35 MaturityDate=20140715 BidPrice=103.948 ...
/FI/EU/B Description=AUSTRIA CpnRate=6.25 MaturityDate=20270715 BidPrice=122.890 ...

/FILTER/META/CTR/FI/ALL/GROUPHEADER/groupby=Description/GERMANY
SubHeaderText=GERMANY
Description=GERMANY /FI/EU/C CpnRate=4.92 MaturityDate=20130328 BidPrice=101.859 ...

/FILTER/META/CTR/FI/ALL/GROUPHEADER/groupby=Description/US TREASURY
SubHeaderText=US TREASURY
/FI/US/A Description=US TREASURY CpnRate=11.25 MaturityDate=20141115 BidPrice=...
/FI/US/B Description=US TREASURY CpnRate=11.75 MaturityDate=20150215 BidPrice=...
/FI/US/C Description=US TREASURY CpnRate=10.62 MaturityDate=20150815 BidPrice=...

Each of the header records (/FILTER/META/CTR/FI/ALL/GROUPHEADER/ and so on) contains one field, SubHeaderText which contains the value of the field that you are grouping by. When the client application receives the sorted and grouped container, it typically displays the header rows in the grid with different styling to the data rows.

The exact way in which record grouping is added to a container subscription depends on which StreamLink library you are using. For details consult the API Reference document for your particular StreamLink implementation.

The container placeholder

The sequence of subscribing to a container, subscribing to all the records in it, and then processing each of the records in Caplin Refiner can take longer than the active request timeout configured in Liberator (see source-request-timeout, add-peer: request-timeout and add-data-service: request-timeout). If Refiner were to wait until it had processed all the results before sending a response, the active request timeout might trigger first and Liberator would then discard the container. To prevent this happening, as soon as Caplin Refiner receives a filter or sort request, it sends back a container response with a single special subject name in it: /FILTER/META/PLACEHOLDER. This is called the container placeholder record. It tells the Liberator that Refiner has received the request, preventing the container subscription from being discarded.

Handling container placeholders in the client

Typically a client application displays a loading indicator while waiting for a response to a sort or filter request, and it removes the loading indicator when a response is received. If the first response received is a container of size 1 that contains a placeholder record, the client should ignore the response and leave the loading indicator in place. The next container update received will either be a container image of the results, or an explicit removal of the placeholder record that indicates there are no matching results. The client should respond by removing the loading indicator.

Caplin Refiner explicitly removes the placeholder record when the time taken to filter the container exceeds the timeout defined in the Refiner configuration property placeholder.timeout. In this case, the client can still receive results from the filter request until such time as it discards the filter.

See also: