Filtering a table region

The examples below use a dataset too large to display fully here. The topic is easier to follow if you download and open THIS EXAMPLE PROJECT, paste the example ATL into a script, and click Preview to see results.

The full dataset has 96 rows. For reference, the first four are:

Suppose you want to calculate the total Sales value for Q2. The first step is to get all rows for which the Quarter value is Q2. You can achieve this with a single-column filter.

Paste this ATL into the example project and click Preview. The result is a filtered table region containing Rows 25–48 (inclusive). Once you have a filtered region, you can use other ATL functions — in this case, columnsInRegion and totalVal — to calculate the Q2 sales total.

[[Q2data = filterRows(WholeTable, 'Quarter', x -> x == 'Q2');
totalVal(columnsInRegion(Q2data, Sales))]]

Now suppose you want the combined sales total for Q2 and Q3. This requires a single-column filter that applies two conditions. Specifically, you want all rows for which the Quarter value is Q2 or Q3. Here is the ATL:

The result is a filtered table region containing Rows 24–72. Again, you could interrogate this filtered region further with other ATL functions to calculate, for example, the combined sales total for Q2 and Q3.

Note that the ATL uses the OR operator (||) to add an extra condition. The other option is to add a condition with the AND operator (&&). This is less common in single-column filters, but here's one example:

The filter applies two conditions. Since these conditions are separated by the AND operator (&&), the row must meet both conditions to pass the filter. When two conditions are separated by the OR operator (||), the row passes by meeting either condition.

Suppose that you want the average Q2 sales value for Boston. This requires a multiple-column filter.

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

You can then interrogate the filtered region to calculate the average sales value.

[[Q2Boston = filterRows(WholeTable, ('City','Quarter'), (x,y) -> x == 'Boston' && y == 'Q2'); mean(columnsInRegion(Q2Boston, Sales))]]

The following filter uses the data in four columns:

[[filterRows(WholeTable, ('Region','Quarter','Sales','Target'), (a,b,c,d) -> a == 'South' && b == 'Q4' && (c > 65000 || percentageChange(c,d) > 5))]]

The above ATL retains all rows for which:

The last example filters the table by using four columns: Region, Quarter, Sales, and Target . These columns are listed in the function's second parameter and are represented by a, b, c, and d in the lambda expression.

Note that you can use full words to represent each column if preferred. For example, this ATL performs the same operation and is probably easier for your colleagues to follow:

[[filterRows(WholeTable, ('Region','Quarter','Sales','Target'), (region,quarter,sales,target) -> region == 'South' && quarter == 'Q4' && (sales > 65000 || percentageChange(sales,target) > 5))]]

For a case-insensitive filter, you must define a condition that is case-insensitive.

Suppose you want all rows for December (Rows 89–96). Most have a Month value of 'Dec', but several have a different value such as 'DEC' or 'dec' . A filter that is case-sensitive is insufficient.

Here is one way to account for the variations in casing:

Now, it doesn't matter if the row's Month value is 'dec', 'Dec', 'DEC', or 'DEc'. All pass the filter because the condition converts the value to lowercase before testing if that value is equal to 'dec' (the constant).

Suppose that you want the total sales value for Q2–Q4 (inclusive). First, you need all rows for which the Quarter value is not Q1. The simplest way to achieve this is to use the negation operator.

Note that this ATL uses the NOT EQUAL TO operator (!=). This is formed by adding the negation operator (!) before an equals sign. You can then interrogate the filtered table to get the total sales value:

[[notQ1data = filterRows(WholeTable,'Quarter', x -> x != 'Q1');
totalVal(columnsInRegion(notQ1data,Sales))]]

You can also use the negation operator before any ATL function that returns a Boolean value of true or false. The negation operator flips true to false, and vice versa. For example:

The first example retains rows with a Month value that starts with the character 'J'. The second does the opposite — that is, it retains rows with a Month value that does not start with 'J'.

A common requirement is to filter OUT rows that are missing a value in a specific column.

You can do this by using the isNotEmpty function in your filter.

For example, suppose you want to remove all rows with no Target value:

Note that Rows 95 and 96 have no Target value; therefore, they don't pass the filter.

Sometimes, you must use isNotEmpty before you can apply another filter condition. For example, suppose you want all rows with a Target value greater than 55,000. You might try a single-condition filter:

Studio returns an error here because when the function evaluates Rows 95 and 96, it finds no value in the Target column, and it cannot assess a non-existent value against the given constant (55000).

You can avoid this by adding an isNotEmpty condition:

[[filterRows(WholeTable,'Target', x -> isNotEmpty(x) && x > 55000)]]

You can combine filterRows with contains to apply less stringent filters. Rather than filtering by looking for an exact match, you can filter by checking whether a column value merely contains the given string value.

To understand this better, compare and contrast these examples:

This first example tests for exact matches. It retains all rows for which the Region value is 'South'.

This second example tests for rows with a Region value that contains the string 'South'. The result is a larger table region because it includes rows with a Region value of 'South' or 'Southeast'.

A more powerful way to use contains is to test if the column value is one of a range of values.

For example, to retain all rows for April–July:

[[

wantedMonths = makeList('Apr', 'May', 'Jun', 'Jul');

filterRows(WholeTable, 'Month', x -> contains(wantedMonths, x))

]]

The first line defines a list of wanted values. The second line filters the array by searching the wanted list for each row's Month value. If the row's value is found in the list, the row passes the filter.

Use the negation operator (!) before contains to apply a does-not-contain filter.

[[

unwantedMonths = makeList('Apr', 'May', 'Jun', 'Jul');

filterRows(WholeTable, 'Month', x -> !contains(unwantedMonths, x))

]]