filter

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".

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

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

to obtain the following:

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.

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