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

ID

Verified

Balance

7

TRUE

0

1

TRUE

1

5

FALSE

54

17

TRUE

35

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.

Height

Age

Age Last Transaction

190

24

18

300

7

7

100

3

3

40

1

2

400

16

16

167

40

38

50

22

20

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

Height

Age

Age Last Transaction

300

7

7

40

1

2

400

16

16

50

22

20


Did this page help you?