MAP function
Source
The array that should be transformed.
ItemTransformer
A formula fragment which is expected to transform the elements of the
given array. To do its work, it has access to the three named values
Element, the array element to be transformed,
Index, the index of the array element (1 for the first
element, 2 for the second element, etc) and finally
Source, which is a reference to the array to transform,
given as the first parameter to MAP.
Returns
The array that results from applying the given transformation to all elements of the given array.
Transforms all array elements using a formula fragment and returns an array with the results. MAP({ 1, 2 }, Element * 10)MAP({ 1; 2 }; Element * 10) multiplies each array element by ten and returns the array { 10, 20 }{ 10; 20 }.
Refer to the documentation for the Body property of email report buttons for a real-world example of how MAP can be used to produce a list of field values for inclusion in an email.
MAP is Calcapp-specific (but is widely used in traditional programming).
MAP versus array-savvy operators
Transforming { 1, 2 }{ 1; 2 } to { 10, 20 }{ 10; 20 } can more easily be done through this formula:
Most operators can be applied to arrays. This formula returns the array { 1, 2 }{ 1; 2 }:
This formula returns the array { 8, 2 }{ 8; 2 }:
This formula returns the array { FALSE, TRUE, TRUE }{ FALSE; TRUE; TRUE }, because only the last two elements are greater than 7:
Most functions can also be applied to arrays. This formula returns the array { 2, 4 }{ 2; 4 }:
MAP is useful when the transformation is more complex. Consider this formula:
The formula above transforms the array { 10, 20 }{ 10; 20 } into the array { 1000, 200 }{ 1000; 200 }. The first array element 10 is multiplied by 100 because it is not greater than 15, and the second array element 20 is multiplied by 10 because it is greater than 15.
MAP and functions which are not array-savvy
Most functions and operators can be used with arrays, but not all. ISERROR, for instance, is not array-savvy, meaning that it always returns TRUE or FALSE. If invoked with an array, it does not return a logical array indicating whether the array elements are errors. Instead, it returns a single logical value indicating whether the array itself is an error.
To solve this, MAP must be used:
The formula above returns the sought logical array, where each element indicates whether the corresponding element in the array given as the first parameter to MAP is an error.
MAP, FILTER and REDUCE
MAP, FILTER and REDUCE are commonly used together, as a data processing pipeline to transform data. FILTER is used to remove irrelevant elements from an array, MAP is used to transform the remaining elements and REDUCE, finally, is used to transform the resulting array to a single value.
Instead of REDUCE, a simpler function can be used which reduces an array of values to a single value. The most popular such functions are SUM (which adds all array elements together and returns the result) and AVERAGE (which returns an average of all the array elements).
A single formula can use multiple invocations of MAP and FILTER. For instance, an innermost FILTER invocation can filter the raw array once, and then hand this data to MAP, which transforms the filtered data. This data can then, once more, be given to FILTER, which filters out additional elements, and so on.
Consider this formula:
The formula above filters the text array { "$326.60", "€402.80", "$290.00", "$128", "3002 SEK" }{ "$326.60"; "€402.80"; "$290.00"; "$128"; "3002 SEK" }, which lists amounts in various currencies, leaving only dollar amounts. It then converts these amounts to numbers, keeps only those amounts exceeding $200 and finally returns the sum of these amounts.
Working our way outwards from the text array, FILTER is applied to it and uses the formula fragment STARTSWITH(Element, "$")STARTSWITH(Element; "$") to only include array elements which start with a dollar sign. That leaves { "$326.60", "$290.00", "$128" }{ "$326.60"; "$290.00"; "$128" }.
MAP is applied to this array, with the formula fragment PARSENUMBER(Element)PARSENUMBER(Element), which converts the text array with textual amounts to a number array holding the same amounts: { 326.6, 290, 128 }{ 326,6; 290; 128 }.
FILTER is then applied to this array using this formula fragment, Element > 200Element > 200, which filters out all elements which are not greater than 200. That leaves the array { 326.6, 290 }{ 326,6; 290 }. Finally, SUM is applied to this array, returning the grand total 616.60.
Examples
Multiplies each array element by ten, and therefore returns the array { 10, 20 }{ 10; 20 }. This can more easily be done through the formula { 1, 2 } * 10{ 1; 2 } * 10.
Transforms { 10, 20 }{ 10; 20 } into { 1000, 200 }{ 1000; 200 }. The first array element 10 is multiplied by 100 because it is not greater than 15, and the second array element 20 is multiplied by 10 because it is greater than 15.
Returns a logical array, where each element indicates whether the corresponding element in the array given as the first parameter to MAP is an error. ISERROR(Field1:Field10)ISERROR(Field1:Field10) does not return a logical array, but rather a single logical value indicating whether the array itself is an error.