Expressions: Aggregations On The Fly - Pattern Match Positional Wildcards

The Pattern Match feature of ‘on the fly’ aggregations supports the following positional wildcards:

Wildcards allow you, for example, to:

select people who bought product A, then another product, and then product A again

With the use of the positional wildcard ?N (where N is a number) at any place in the pattern, it is now possible to refer to other items within the Pattern Match sequence and say that they are explicitly the same as, or different to, others within the sequence.

When FastStats first encounters a pattern - ?1 – it stores that value, e.g. Destination of United States. Subsequent references later in the pattern will always return the same value. Any other ?N must, therefore, match a different value, i.e. ?2 in this example could not be United States.

Example Pattern Match sequence:

Australia - ?1 - Australia - ?1 - Australia

This will select and return people who have been to Australia, then somewhere else, then back to Australia, then back to the same somewhere else, and then Australia.

Or:

?1 - ?2 - ?3 - ?2 - ?1

This will select and return people who have been to Destination 1, then Destination 2, then Destination 3, then back to Destination 2 and back to Destination 1.

?1 means first in the defined pattern - and not necessarily first in a list - of transactions.

Let’s consider an example in practice:

Open a new Expression window and click on the Add Aggregation icon

To access the ‘on the fly’ options:

Click onto the Frequency(Bookings) tab
Select Pattern Match from the Type drop-down menu options

Set the Grouping Table to People and the Transactional Table to Bookings

Order records by Booking Date

Add Destination as the Pattern Match Variable

Click Set Pattern to open the Define pattern for Destination window

Click on the drop-down menu in the Value 1 cell to access the available options:

Select ?1

Wildcard numbers must be introduced into the pattern in order. Once selected, when you subsequently click onto the Value 2 cell, the options to select ?1 and ?2 will be available, etc. It is not valid to enter ?1 - ?3 - ?5.

Complete the settings and enter the Pattern Name as in the screenshot below

The pattern matched for each person will be dependent upon the value recorded against ?1. The screenshot below demonstrates this:

Person URN 119704: United States-Australia-United States
Person URN 119708: Italy-France-Italy
Person URN 119753: Italy-Portugal-Italy

These wildcards can be used with:

Fixed values

e.g. ?1 Australia ?1

Returns: Destination 1, then Australia, then back to Destination 1

= wildcard

e.g. ?1 = ?2 =

Returns: Destination 1, then Destination 1 again, then Destination 2, then Destination 2 again

? wildcard

e.g. ?1 ? ?2

Returns: Destination 1, then anywhere (that could be Destination 1), then Destination 2

* wildcard

e.g. ?1 * ?2 * ?1

Returns: Destination 1, then any number of Destinations (that could be Destination 1), then Destination 2, then any number of Destinations (that could be Destination 2), then Destination

N must be in the range from 1 to 255, which is the maximum pattern length permitted. N > the number of codes for a variable will never match.