MAP and MAPFLAT

This article describes basic MAP functionality and also advanced usage with dynamic and XML/JSON data sources.

MAP( values, variable name, expression)

MAPFLAT( values, variable name, expression)

The MAP function is used to repeat a formula over a set of values. It assigns, one at a time, values specified by the first parameter to variable name 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, (x). For instructions on creating variables, see Variables.

The MAP function returns a single value for each of the values provided in the first parameter.  The MAPFLAT function returns an set of values for each of the values provided in the first parameter.

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.




MAP( ARRAY( apples, blueberries, pears ),
         "fruit",
         SUM( SELECT( C:C, 
A:A = fruit  ) ) )

returns  (33, 42, 35)

Example 2


The MAP 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 MAP function can be used to return all the flower colours in each garden. Note the use of the JOIN function to combine results into comma-separated values.

MAP( A:A, "var", JOIN( LOOKUP( SELECT( B:B, (C:C = var) ), D:D, E:E ) ) )

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

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 )

  • The MAP function returns only one value for each expression result. If expression returns an array of values, only the first item in the array is returned by the MAP function. To return the entire array of expression results for each instance of variable name, you must wrap the JOIN function around expression, to combine the results into a single, comma-separated value. For example, using the following data source:

 

The formula

     MAP( ARRAY( apples, blueberries, pears ), "fruit", SELECT( SLICE( B:B ), ( SLICE( A:A ) = fruit ) ) ) 

returns ( 
British Columbia
              Manitoba
              British Columbia )

while the formula 
     MAP( ARRAY( apples, blueberries, pears ), "fruit", JOIN( SELECT( SLICE( B:B ), ( SLICE( A:A ) = fruit ) ) ) )

returns ( British Columbia,Ontario,Nova Scotia

              Manitoba,Prince Edward Island,Saskatchewan
              British Columbia,Ontario )

Advanced Uses


The MAP function is particularly useful when working with dynamic data sources and JSON/XML data sources.

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; ))

JSON/XML Data Sources


JSON and XML data sources can require special handling to access their arrays and objects. This typically involves editing the XPath to traverse the data hierarchy. In this scenario, the DATASOURCE function can be useful.

Examples

  • Where LinkedInDsid is the data source ID of a LinkedIn data source, the following retrieves the last name of all connections with first-name = Dave.
     DATASOURCE( LinkedInDSid, "/connections/person[first-name='Dave']/last-name ]" ) 

This formula can be made more flexible by using the MAP and CONCAT functions. Note the use of the JOIN function to 
combine results into comma-separated values. 

     MAP( ARRAY( Dave, Mary ), "name", JOIN( DATASOURCE( LinkedInDSid, CONCAT( "/connections/person[first-name='", name "']/last-name ]" ) ) )
  • Where FacebookDSid is the data source ID of a JSON format Facebook data source, the DATASOURCE function is used to return the name of an individual object, in this example, data/values[1]/value[1]/*[1].
     DATASOURCE( FacebookDSid, "name(/data/values[1]/value[1]/*[1])" )  

     returns F.13-17

 

Use the MAP function to return the names of all objects in the array, data/values[1]/value[1]/*.

 

     MAP( CUMULATIVE(REPEAT( 1, COUNT(/data/values[1]/value[1]/*) ),

               "index",
               DATASOURCE( FacebookDSid, CONCAT( "name(/data/values[1]/value[1]/*[", index, "])" ) )

               returns ( F.13-17
                              F.18-24
                              F.25-34
                              F.35-44
                              F.45-54
                              F.55-64
                              F.65+
                              M.13-17
                              M.18-24
                              M.25-34
                              M.35-44
                              M.45-54
                              M.55-64
                              M.65+ )

Related links: