Filter and Sort in Refiner

Here's how to build filter, sort and grouping expressions for Refiner.

If you want to use Caplin Refiner from another DataSource application, you create a subscription request that contains the Refiner parameters and send the request to the appropriate Transformer to be processed by its Refiner module. The subject of the subscription is the name of the container to be processed, with the prefix /FILTER and a trailing ? character followed by the filter/sort/grouping expression. Each expression is separated from its neighbour by a semicolon (;)

For example, the request could be:

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

The rules described on this page use this syntax. However, when you use Refiner from a client application via StreamLink, you set up the filter, sort and grouping expressions using Streamlink API calls and then the syntax is slightly different; for more about this, see How can I... Access Refiner through StreamLink.

Warning! 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.
Tip: 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).

Contents:

Filter expressions

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:

filter=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 be compound expressions, joined with & (AND operator) or | (OR operator).
For example:

filter=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:

Check mark (tick) (a & b) | c

 Check mark (tick) a & (b | c) 

Cross a & b | c 

Sort expressions

To sort container elements, you use API calls to build up a sort expression that's then sent to Caplin Refiner. Container sort expressions 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 expression:

sort=MaturityDate:number:ascending

This sort expression 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

In versions of refiner prior to 7.0.0, 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:


In Refiner 7.0.0 and later, you can sort on multiple fields. Thus, when two or more records have the same value in the first sort field, the next sortable field is used for comparison. If no more sortable fields exist, the natural ordering is used, as explained above.
The precedence of the sort criteria is defined by the order they are provided in the query. Each sort criterion must start with sort= and you use a semicolon (;) to separate multiple criteria. For example:

sort=MaturityDate:number:ascending;sort=CpnRate:number:descending

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/CTR/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.

Tip: Within Streamlink, 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-peerrequest-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: