# MAPFLAT and MAP

The MAPFLAT function is used to repeat a formula over a set of values. It assigns values specified by the first parameter to variable name, one at a time, and for each value, executes the formula specified by expression. Note that the second parameter, variable name, must be entered as a literal string (without quotes) while the variable used in expression is entered as a \$variable . For instructions on creating variables, see Variables.

Syntax and Parameters: `MAPFLAT( values, variable name, expression)`

Note: We recommend using MAPFLAT rather than MAP because MAPFLAT supports returning multiple items per 1st parameter while MAP returns only 1 item.

## Example 1

The following example assigns each item in `ARRAY( apples, blueberries, pears )` to the variable, fruit, then sums all items in column C where the corresponding item in column A = \$fruit . `MAPFLAT( ARRAY( apples, blueberries, pears ),`
`"fruit",`
``` SUMIF ( @A:A = \$fruit, @C:C ) ) ```

returns:  (33, 42, 35)

## Example 2

The MAPFLAT function can be used to retrieve data with a many-to-many relationship. For example, a garden can have many flowers and a flower may be planted in many gardens. Using the following data source: `LOOKUP( @A:A, @C:C, LOOKUP( @B:B, @D:D, @E:E ) )`

returns only the colour of the first flower found for each garden in column A:
( red, red, purple, purple, blue )

The MAPFLAT function can be used to return all the flower colours in each garden.

``` MAPFLAT( @A:A, "var", LOOKUP( SELECT( @B:B, (@C:C = \$var  ) ), @D:D, @E:E ) ```

returns: ( red,blue,pink
red,purple,blue
purple,blue
purple,blue
blue,yellow )

### Notes

• The first parameter, values, must not be null otherwise the function will return an error. For example, the following formula will return an error because the first SELECT returns 0 items.

`MAP( SELECT( ARRAY( apples, blueberries, pears ), ( ARRAY ( apples, blueberries, pears ) = bananas) ), "fruit", `       ``` SUM( SELECT( @C:C, @A:A = \$fruit  ) ) ) ```

returns:  (33, 42, 35)

To work around this, check the number of items returned by the SELECT before executing the MAP function:

`IF( COUNT( SELECT( ARRAY( apples, blueberries, pears ), ( ARRAY( apples, blueberries, pears ) = bananas) ) ) 0,`

`MAP( SELECT(ARRAY( apples, blueberries, pears ), ( ARRAY( apples, blueberries, pears ) = bananas) ),`

`"fruit",`
``` SUM( SELECT( @C:C, @A:A = \$fruit  ) ) ), 0 ) ```

## Example 3

Combine the results into a single, comma-separated value. For example, using the following data source: ``` MAPFLAT( ARRAY( apples, blueberries, pears ), "fruit", SELECT( @B:B, @A:A = \$fruit  ) ) ```

returns: ( British Columbia,Ontario,Nova Scotia
British Columbia,Ontario )

### Dynamic Data Sources

The MAP function can be used to repeat a formula for a set of dynamic data source instances, where the variable specified in the MAP function is the variable used to create the dynamic data source ( for more information, see Dynamic data sources ).

The following example uses a Google Analytics dynamic data source that uses the variable, gaID, for the Profile ID:

`https://www.googleapis.com/analytics/v3/data/ga?ids=ga:{props.gaID}&dimensions=ga:country&metrics=ga:sessions&start-date={date.add(-30).format()}&end-date={date.today}&max-results=10000`

and returns data in the following format: The following MAP function returns the total number of sessions, `SUM( SLICE( @B:B; ) )`, for each dynamic data source instance created by setting gaID to gaID1, gaID2 and gaID3:

`MAP( ARRAY( "gaID1", "gaID2", "gaID3"), "gaID", SUM( SLICE( @B:B )))`

The following MAPFLAT function returns the sessions for each country, `SLICE( @B:B; )`, for each dynamic data source instance created by setting gaID to gaID1, gaID2 and gaID3

`MAPFLAT( ARRAY( "gaID1", "gaID2", "gaID3"), "gaID", SLICE( @B:B ))`