.. index:: Ledger Filtering, Query Expressions; Ledger Data

Ledger Filtering
================

All data (including loss events and all other events) flowing through the business model defined in the Data API can be filtered.
Filtering can be done at links or at nodes using the :ref:`Node <filter-nodes>` and :ref:`Link <filter-links>` structures.

.. index:: Ledger Filtering; Node Filters
.. _filter-nodes:

Data API Filter nodes
---------------------

The following example structure defines an intermediary ``Node`` that filters records before they arrive at the ``QuotaShare`` ``Node``
for application of financial terms.

Refer to the below :ref:`filter-expressions` for examples of the operations supported.

.. graphviz:: ledger_data_example1.dot

.. index:: Ledger Filtering; Link Filters
.. _filter-links:

Data API Filter links
---------------------

As a convenience method for filtering without having to define additional ``Nodes`` in a business model, filtering can be applied to ``Links`` by defining the attributes on a ``Link`` as in the following example:

.. graphviz:: ledger_data_example2.dot

In the example, there are three attributes specified in ``LinkFilter_2.0`` template: ``expression``, ``include_record_types``, ``scale_value``. They correspond to
three types of operations supported in a ``LinkFilter_2.0``: `filter expressions`, `record types filtering` and `scaling`.

.. note:: The attributes in the ``LinkFilter_2.0`` are all optional.

The `filter expressions` operations are identical to those supported at the ``Node`` level.  Refer to the below :ref:`filter-expressions` for examples.

The `record types filtering` operation filters records with certain record type(s). A user can specify a list of record types under the attribute ``include_record_types``
to allow records that matches with any specified record types output from the ``Link``. Internally, each record type in the ``include_record_types``
will be transformed to a ``Node`` that uses :ref:`core-operation-record-type-filter` with ``EQUAL`` as ``op``.

The `scaling` operation applies a scaling factor specified by ``scale_value`` to the value of each record in the ledger, which uses :ref:`core-operation-scale`.

.. note:: Unlike defining a filter on a ``Node``, this invokes a special template that replaces the ``Link`` with one or more intermediate ``Node(s)``,
 since internally, all operations in the financial model are performed at ``Nodes``.


.. index:: Ledger Filtering; Example Expressions
.. _filter-expressions:

Filter Expressions
------------------
Filter Expression can use the valid field names and their corresponding values found in the ledger parquet files.

The query expressions provided in :ref:`Node <filter-nodes>` and :ref:`Link <filter-links>` structures are based on the `SQLite expression syntax <https://www.sqlite.org/syntax/expr.html>`_

The syntax for these expressions is not to be confused with the syntax used to define :ref:`Data API Queries <element-expressions>`, although they are similar.

Matching and direct comparison
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+--------------------+-------------------------------------------------------------+
| Expression example | Meaning                                                     |
+====================+=============================================================+
| ``Region='SC'``    | The ``Region`` attribute matches ``SC`` exactly             |
+--------------------+-------------------------------------------------------------+
| ``EventId=6``      | The ``EventId`` attribute is 6                              |
+--------------------+-------------------------------------------------------------+
| ``Region<>'SC'``   | The ``Region`` attribute is not ``SC``                      |
+--------------------+-------------------------------------------------------------+
| ``Intensity<6.5``  | The ``Intensity`` attribute is less than 6.5                |
+--------------------+-------------------------------------------------------------+
| ``Intensity>=4.0`` | The ``Intensity`` attribute is greater than or equal to 4.0 |
+--------------------+-------------------------------------------------------------+

Matching multiple possible values
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+---------------------------------+-----------------------------------------------------------------------+
| Expression example              | Meaning                                                               |
+=================================+=======================================================================+
| ``Peril in ('HU', 'FFEQ')``     | The ``Peril`` attribute matches either ``HU`` or ``FFEQ``             |
+---------------------------------+-----------------------------------------------------------------------+
| ``Peril not in ('HU', 'FFEQ')`` | The ``Peril`` attribute does not match either of ``HU`` or ``FFEQ``   |
+---------------------------------+-----------------------------------------------------------------------+
| ``EventId in (1,3,5,7)``        | The ``EventId`` attribute does not match either of ``HU`` or ``FFEQ`` |
+---------------------------------+-----------------------------------------------------------------------+

Null comparison
~~~~~~~~~~~~~~~

+--------------------------+-----------------------------------------------------------------------+
| Expression example       | Meaning                                                               |
+==========================+=======================================================================+
| ``Peril is null``        | The ``Peril`` attribute is _null_                                     |
+--------------------------+-----------------------------------------------------------------------+
| ``Peril is not null``    | The ``Peril`` attribute is not _null_                                 |
+--------------------------+-----------------------------------------------------------------------+
| ``EventId in (1,3,5,7)`` | The ``EventId`` attribute does not match either of ``HU`` or ``FFEQ`` |
+--------------------------+-----------------------------------------------------------------------+

Logical expression combinations
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+---------------------------------------------+-----------------------------------------------------------------------------------------------------+
| Expression example                          | Meaning                                                                                             |
+=============================================+=====================================================================================================+
| ``Region='FL' and Peril in ('HU', 'FFEQ')`` | The ``Region`` attribute matches ``FL`` and the ``Peril`` attribute also matches ``HU`` or ``FFEQ`` |
+---------------------------------------------+-----------------------------------------------------------------------------------------------------+
| ``(not Region='FL') or Peril='HU'``         | The ``Region`` attribute does not match ``FL`` or the ``Peril`` attribute matches ``HU``            |
+---------------------------------------------+-----------------------------------------------------------------------------------------------------+
| ``not (Region='FL' and Peril='HU')``        | The record matches when the ``Region`` is not ``FL`` or the ``Peril`` is not ``HU``                 |
+---------------------------------------------+-----------------------------------------------------------------------------------------------------+