Expressions: Aggregations on the fly - Pattern Match Include or Exclude List

It is possible to define include and exclude lists when working with selector or numeric pattern match aggregations.

Selector

Let's imagine that you want to identify people who have repeatedly visited the same European destination.

To create the pattern match:

Open a new Expression and click onAdd Aggregation button and then on the tab entitled Frequency(Bookings) that opens up.
In the Type drop down select Pattern Match.
Set the Grouping Table to People and Transactional Table to Bookings.
Drag the Booking Date variable onto the Order records by drop-box and leave the From as Earliest to Latest.
Drag the Destination selector variable onto the Pattern Match Variable drop box.

The above settings allow us to search for patterns in people's holiday choices, ordered by their booking date.

Click Set pattern - a new window will open.

By default, patterns using a selector variable default to displaying Descriptions

Click on the Value 1 cell drop-down menu and select =.
Right-click on the Value 1 cell to access the copy and fill options - select Fill cells across and down.

We know that the maximum number of bookings any one person has made on the Holidays system is 8, so:

Complete settings as per the screenshot below and OK.

By default, the system will look for your defined pattern across all destinations. However, to limit this, you can choose to include or exclude specific categories using the and icons at the bottom of the window. For example, to only include European destinations visited:

Click on the include list icon
Select the required categories from the left-hand column and drag them across to the Pattern Code List column on the right

Now only European destinations will be considered for the longest sequences of the same destination visited.

Numeric

It is possible to select a set of numeric ranges for an include or exclude list when defining a numeric pattern match aggregation. This allows you to answer questions such as, what is the longest sequence of holidays a person has had, excluding any that cost less than £500 or more than £1000.

To create the pattern match:

Open a new Expression and click onAdd Aggregation button and then on the tab entitled Frequency(Bookings) that opens up.
In the Type drop down select Pattern Match.
Set the Grouping Table to People and Transactional Table to Bookings.
Drag the Booking Date variable onto the Order records by drop-box and leave the From as Earliest to Latest.
Select Pattern Match Numeric Variable and then drag the Cost numeric variable onto the Pattern Match Variable drop-box.
Set the type to Longest Strictly Increasing.
Click Exclude List and enter the required ranges:
Name the expression and then drag and drop it onto a data grid to review the results.

The highlighted example demonstrates that, with the exclude list applied, this person has 2 holiday bookings where the cost is strictly increasing.