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 (with 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)
Notes:
- MAPFLAT and MAP are rarely used and typically only used for the cases described below.
- We recommend using MAPFLAT rather than MAP because MAPFLAT supports returning multiple items per 1st parameter while MAP returns only 1 item.
- MAPFLAT can result in your formulas taking longer to execute depending on the complexity and total number of values.
Dynamic data sources
The MAPFLAT and MAP functions can be used to repeat a formula for a set of dynamic data source instances, where the variable specified in the MAPFLAT or MAP function is the variable used to create the dynamic data source. For more information, see Dynamic data sources.
The following example uses a Facebook Ads dynamic data source that uses the variable, campaignID, for the campaign ID:
https://graph.facebook.com/v19/{props.campaignID}/insights?fields=impressions,name,ad_id&level=ad
The above query returns a JSON response with values for impressions, ad Id, and campaign name.
The following MAP function returns the total number of impressions, SUM(@data/impressions;) for each dynamic data source instance created by setting campaignID to campaignID1, campaignID2, and campaignID3:
MAP( ARRAY( “campaignID1” , “campaignID2” , “campaignID3”), “campaignID” , SUM(@data/impressions;) ) )
The following MAPFLAT function returns the impressions for each ad ID, @data/impressions; for each dynamic data source instance created by setting campaignID to campaignID1, campaignID2, and campaignID3:
MAPFLAT( ARRAY( “campaignID1” , “campaignID2” , “campaignID3”), “campaignID” , @data/impressions; ) )
Paginated data sources
You can also use dynamic data sources when you need to get more than one page of data. You can set up your data source to have a page or offset value as the dynamic property of the data source.
To access multiple pages of data, you must create two data sources:
- A static data source query that returns the first page of data.
- A dynamic data source query that uses a variable (for example, pageNumber or recordNumber) to create multiple instances of the query, one for each page of data.
Because paginated data is contained in multiple data sources, the full set of records cannot be selected using a single data reference. Instead, the data must be aggregated using the MAPFLAT function. See this article for more details and examples.
JSON and XML xpath manipulation
In rare cases, based on the xpath structure of your data, you may need to use a MAPFLAT to loop through the JSON elements and get the data for each. There are many ways to work with JSON data that should be used first and these are outlined here.