filter

The filter directive is powerful. It allows the user to selectively choose data rows from a worksheet. Being able to select rows from a worksheet can simplify data processing.

The filter directive allows a user to select rows from a worksheet for further processing. Being able to focus on a select set of rows in a worksheet can make processing the data in that worksheet simpler. For instance, a user could filter out high-value customers from a list of customers, or in another scenario, filter out "buy" transactions from a list of stock transactions.

📘

Filter is a transformative directive

The "filter" directive requires a declared "from" directive to be present in the same comment.

In its simplest form, the filter directive only requires the column header name in order to work. Consider a worksheet that contains an column named "Active", where the values in this column are "true" or "false". then the following "filter" declaration:

pebblestream:from(Customer)
pebblestream:filter("Active")

will cause Pebble Stream to only include rows where the value in the "Active" column is true.

The filter directive can also perform more sophisticated selections. Consider a "Product" worksheet that has columns "Amount", "Color", and "Balance". In order to select blue products that have amounts greater than 0 and less than the corresponding value in the "Balance" column we can say the following:

pebblestream:from(Product)
pebblestream:filter("Amount", ">0", "Color", "blue", "Amount", "<="&"Balance")

This declaration will only include rows where the "Amount" is greater than 0, the "Color" is "blue", and the "Amount" is less than or equal to the value in that row's "Balance" column.

📘

To implement "OR" conditions, use multiple "filter" declarations.

The filter directive could be used more than once in a comment.

Example 1

Consider the worksheet "Accounts".

IDVerifiedBalance
7TRUE0
1TRUE1
5FALSE54
17TRUE35

To select the verified accounts use the following declaration in the "Verified" worksheet:

pebblestream:from(Accounts)
pebblestream:filter("Verified")

to obtain the following:

ID
7
1
17

Example 2

Consider a more complex example. Given a worksheet of "Heights", find unusual data points where the subject appears to be underweight, or quite tall, or the measurement time is questionable.

HeightAgeAge Last Transaction
1902418
30077
10033
4012
4001616
1674038
502220

Applying the following directive declaration in the "Unusual" worksheet:

pebblestream:from(Heights)
pebblestream:filter("Height", ">200")
pebblestream:filter("Height", "<60", "Age", ">3")
pebblestream:filter("Age Last transaction", ">"&"Age")

obtains the following rows

HeightAgeAge Last Transaction
30077
4012
4001616
502220