filterRows

These examples show how to apply a single-condition filter. This can be done with a helper function in the third parameter, but you can use a lambda expression if you prefer. We've provided example ATL for both.

To retain rows with a Branch value equal to 'Boston':

[[filterRows(WholeTable, Branch, eq('Boston'))]]

OR

[[filterRows(WholeTable, Branch, x -> x == 'Boston')]]

The filtered table region:

To retain rows with a Sales value greater than or equal to 400,000:

[[filterRows(WholeTable, Sales, gte(400000))]]

OR

[[filterRows(WholeTable, Sales, x -> x >= 400000)]]

The filtered table region:

To apply two or more filter conditions, you must use a lambda expression in the third parameter.

When filtering by multiple columns, the input to the second parameter must be a list. These examples use a list of strings (column names) rather than a list of column variables, but you can use either.

To retain rows with a Quarter value equal to 'Q2' AND a Sales value greater than 300,000:

[[filterRows(WholeTable, ('Quarter', 'Sales'), (x,y) -> x == 'Q2' && y > 300000)]]

The filtered table region:

To retain rows when Quarter = 'Q2' AND Sales exceeds 300,000 AND Sales exceeds Target:

[[filterRows(WholeTable, ('Quarter', 'Sales', 'Target'), (x,y,z) -> x == 'Q2' && y > 300000 && y > z)]]

The filtered region:

You can use other ATL functions to help define filter conditions. For example, to retain rows where Sales exceeds 350,000 AND Sales exceeds Target by more than 12.5%, you can use the percentageChange function.

[[filterRows(WholeTable, (Sales, Target), (x,y) -> x > 350000 && percentageChange(x,y) > 12.5)]]

The filtered table region:

You can interrogate a filtered table region with other ATL functions.

Here's how you might get a sales performance summary for Q1:

[[

Q1data = filterRows(WholeTable, 'Quarter', eq('Q1'))

Q1sales = totalVal(columnsInRegion(Q1data, Sales))

Q1target = totalVal(columnsInRegion(Q1data, Target))

performance = percentageChange(Q1sales, Q1target)

direction = performance > 0 ? 'exceeded' : 'fell short of'

joinStrings('Q1 sales ', direction, ' the target by ', abs(performance), '%.')

]]

The output text: