What is the Smart Filter Rows Command and how do I use it?
The Smart Filter Rows Command is a powerful mechanism to filter a data set based on multiple criteria. When we leverage the Smart Filter Rows Command, we need to consider several operators. First, the Inverse parameter determines how the filter functions. When Inverse is selected, the rows that match the filter criteria are the resulting output. If Inverse is not selected, then the rows matching the filter are removed from the resulting output.
Next, the filter criteria needs to be established. There are three types of filter criteria that can be created - Text, Date, and Number. Multiple criteria can be specified within each filter type.
Additionally, for each filter type (Text, Date, Number), the operator for the criteria can be specified as AND or OR. When the AND operator is specified, each of the criteria in the type filter must be met while specifying OR means that when any of the criteria met, the filter is applied.
Within the Text filter type, there are additional operators that enable the filter to be more dynamic. The NOT operator provides the inverse of the Condition (Equals, Contains, Starts with, Ends with) and the Trim operator removes any leading or trailing spaces on the text to be evaluated. The Date and Number filter types also support the NOT operator. Additionally, the Number filter type provides an additional Absolute Value operator. This operator provides an easy way to filter ranges of values.
Lastly, the operator for the collection of filters must be specified. As with the operator on the individual filter types, the collection of filters across the different types are evaluated in the AND or OR conditions.
Let's apply this knowledge to the following example. A sample data set contains data for multiple scenarios (Actual, Plan, Forecast), multiple fiscal years and has both zero and non-zero data records. Our requirement is to produce a data file that contains Budget and Forecast data for the current year and excludes zero value records.
As we devise our filters, we need to be particularly aware of the AND/OR operators as well as the Inverse setting. For larger data sets, it's more straightforward to retain the records that match the filtering criteria rather than attempt to remove all the potential combinations. As such, we'll check the Inverse option to tell the Smart Filter Rows Command to keep the rows matching the filter criteria.
Next, we understand functionally that the data can contain data for three scenarios - Actual, Plan, Forecast. To isolate the data to just Plan and Forecast, we specify a text filter to capture any records where the Scenario is NOT Equal to Actual (OEP_Actual). By specifying a not equal condition, all rows where the Scenario column has a value other than OEP_Actual, will match the criteria and because of the Inverse setting will be preserved. Additionally, we set a text filter criteria to capture data for the current year with a filter where the Year column Equals FY20. We hard-coded the value of FY20 in this example but this could easily be a Workspace, Environment, or Chain Variable or an output from a previous Command. This filter will identify all records where the Year column has a value of FY20 and the rows will be preserved. Since we want the intersection of these text filter criteria, meaning both conditions are met, we set the Text filter criteria as AND.
Next, we define a Number filter to remove zero value records. This filter can be specified in two different but equal ways. First, we could set the criteria to filter the Amount column where the Decimal value is NOT Equal to 0.00. This would identify all of the non-zero records. The other option leverages the Absolute Value operator in combination with the Greater Than condition. In this example, the filter is set that the Amount column Decimal value has an absolute value greater than 0.00. This also identifies any non-zero value records and preserves them because of the Inverse operator.
The latter approach provides the ability to specify a range of values to be removed from the data set. For example, if the goal were to remove any record that is between -0.01 and 0.01, then the latter approach provides a more efficient way to achieve this. In this exercise, we have used the latter to allow us to leverage ranges in the future.
Finally, we need to determine the the global operator for these various filters. To recap, the following criteria exist:
- Remove the Actual Scenario and (yellow)
- Keep Only the Current Year and (blue)
- Remove all zero records/Keep only non-zero records (green)
Because all of these criteria must be met, our global filter setting must be AND.
The result of this Smart Filter Rows Command on a sample data set is below.
The Smart Filter Rows Command is an efficient and powerful way to split data extracts into functional data sets that can be used across various Enterprise systems. If you have any questions about Smart Filter Rows, please feel free to comment here or contact us at email@example.com.