Filtering

There are times when you might want to exclude part numbers from the results. This can be done by a “Filter” expression added to one or more variables in the Template section.

For example:

- {Name: $PARTNUMBER, Type: "date", Required: true, Filter: "$POP_CODE in ['A','B']"}

If more than one Filter is defined, all must be satisfied in order for the row to be included. If there is a chance that the source data is empty, you must add a check for “nil” to keep the process from attempting to compare a nil value.

For example:

Filter: "$POP_CODE != nil && $POP_CODE in ['A','B']"

This tells the processor to first check that the pop code is not empty, before attempting the “in” operation. The “&&” means AND which will exit (“sort-circuit”) the evaluation if evaluated to false.

Runtime Variable

Another common use for a filter is to provide a runtime variable (passed as a parameter) that is then used for comparison in the filter expression.

For example:

Filter: "$EFF_DATE != nil && $EFF_DATE < Date(cutoff)"

Here, the cutoff value used in the filter was passed into the process with the following command:

loadsheets -config filename.yaml -params "cutoff=2020-12-05"

The Date() function takes a string in yyyy-mm-dd format and converts it to an actual date.

Since this is such a common pattern, we also provide a built-in function that simplifies the Filter somewhat:

Filter: "DateOnOrBefore($EFF_DATE, cutoff)"