mapRows

Loops through rows in a table or table region, applies a mapping function to the values in one or more columns, and returns the amended table.

The first parameter takes a table region, the second specifies the column to map, and the third defines the function to apply to the column values.

This function may be useful if you want to amend or clean data in a table column; for example to convert inconsistent entries or replace all instances of a value.

Note

Note that this function cannot be used to change the values in the row names column (the first column).

Note

Available in "Describe the Table" and "Describe Row in Context" projects only.

Parameters

INPUT TABLE (table region)
The table region to map. Can be a table or table region returned from a function. Use WholeTable to map the whole table.
COLUMNS (column, string, or list)
The column(s) whose values should be replaced if appropriate. Give either the column variable or the column name as a string. When using multiple columns, give each variable/name in a list. See the Examples for guidance.
MAP FUNCTION (function)
A function to define and return the replacement for each column value. The function can be conditional. The function can be a lambda function or a user-defined function (UDF) that takes one parameter.

Examples

Assume a "Describe the Table" project with this data:

	Code	Quarter	Country	City	Sales	Target
Row 1	1001	Q1	France	Paris	-	-
Row 2	2001	Q1	France	Lyon		350,000
Row 3	3001	Q1	u.k.	London	422,245.33	400,000
Row 4	4001	Q1	united kingdom	Edinburgh	346,166.18	350,000

In the table above, the Country column uses inconsistent formats, the Sales column has hyphen in Row 1 and an empty cell in Row 3, and the Target column has a hyphen in Row 1. It would be awkward to pass this data into other functions, such as filterRows. Using mapRows, you can amend or replace values to clean the data, making it easier to pass it into other functions.

Important

To retrieve the cell values from each cell of the column(s), use the value function in your comparisons: see the examples below for guidance.

Mapping with single conditions

These examples show you how to use mapRows to identify and replace specific values from a single column.

This example replaces a hyphen, such as in Row 1. Here, the mapping function is a conditional statement in ternary form, which identifies the matching condition and the replacement values if the column value matches or doesn't match:

[[mapRows(WholeTable, 'Sales', x -> value(x) == '-' ? '0' : x)]]

The output text is an amended table:

	Code	Quarter	Country	City	Sales	Target
Row 1	1001	Q1	France	Paris	0	-
Row 2	2001	Q1	France	Lyon		350,000
Row 3	3001	Q1	u.k.	London	422,245.33	400,000
Row 4	4001	Q1	united kingdom	Edinburgh	346,166.18	350,000

To replace a null, such as shown in Row 2:

[[mapRows(WholeTable, 'Sales', x -> value(x) == null ? '0': value(x))]]

You can also use an ATL function in the condition. For example, this uses isEmpty to identify empty cells:

[[mapRows(WholeTable, 'Sales', x -> isEmpty(value(x)) ? '0': value(x))]]

Both examples will return the following amended table:

	Code	Quarter	Country	City	Sales	Target
Row 1	1001	Q1	France	Paris	-	-
Row 2	2001	Q1	France	Lyon	0	350,000
Row 3	3001	Q1	u.k.	London	422,245.33	400,000
Row 4	4001	Q1	united kingdom	Edinburgh	346,166.18	350,000

Mapping with multiple conditions

You can use mapRows with multiple conditions. This may be useful if you want to amend or replace multiple values in a single column, for example, to identify and replace all instances of inconsistent formatting:

[[mapRows(WholeTable, Country, x -> (lower(value(x)) == 'united kingdom' || lower(value(x)) == 'u.k.') ? 'United Kingdom': value(x))]]

The output text is:

	Code	Quarter	Country	City	Sales	Target
Row 1	1001	Q1	France	Paris	-	-
Row 2	2001	Q1	France	Lyon		350,000
Row 3	3001	Q1	United Kingdom	London	422,245.33	400,000
Row 4	4001	Q1	United Kingdom	Edinburgh	346,166.18	350,000

Mapping multiple columns

You can map the values of more than one column. To do this, give a list of the column names or column variables as the argument to the second parameter. For example:

[[mapRows(WholeTable, ('Sales', 'SalesTarget'), x -> value(x) == '-' ? '0': x)]]

The output text is:

	Code	Quarter	Country	City	Sales	Target
Row 1	1001	Q1	France	Paris	0	0
Row 2	2001	Q1	France	Lyon		350,000
Row 3	3001	Q1	u.k.	London	422,245.33	400,000
Row 4	4001	Q1	united kingdom	Edinburgh	346,166.18	350,000

mapRows

Note

Note

Parameters

Examples

Important

Mapping with single conditions

Mapping with multiple conditions

Mapping multiple columns

Search results