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
                Manitoba,Prince Edward Island,Saskatchewan
                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 ))

Related links: