Grouping

Assume a "Describe the Table" project with this data:

Suppose you want to find which Region achieved the highest Sales total.

The best solution is to use groupByTable. The ATL is:

[[groupByTable(WholeTable, Region, Sales, sum())]]

This tells Studio to analyze the whole table, group all rows with matching Region values, and aggregate the Sales values for each group. The final parameter input — sum() — specifies sum aggregation.

The output is this list of lists:

There are three inner lists — one for each group — and each contains two values: the group's Region value and its sum-aggregated Sales value. The results are sorted by the aggregated values in descending order.

The printed result is:

Here's how you might turn the result into coherent narrative text:

[[

allResults = groupByTable(WholeTable, Region, Sales, sum())

topResult = allResults[0:1]

topRegionName = topResult[0][0]

topRegionSales = abbreviateNumber(currencyFormat(topResult[0][1]))

joinStrings('the highest Sales total (', topRegionSales, ') was in the ', topRegionName, ' region.')

]]

The printed result is:

This example has one grouping column (Region), one aggregation column (Sales), and one aggregation type (sum). The groupByTable function can perform more complex analyses — see the function topic for examples.

Assume a "Describe a JSON Object" project with this data:

{
  "data": [
    {"ID": 1, "City": "Atlanta", "Region": "South", "Quarter": "Q1", "Year": 2022, "Sales": 56000},
    {"ID": 2, "City": "Los Angeles", "Region": "West", "Quarter": "Q1", "Year": 2022, "Sales": 52000},
    {"ID": 3, "City": "Dallas", "Region": "South", "Quarter": "Q1", "Year": 2022, "Sales": 44000},
    {"ID": 4, "City": "New York", "Region": "East", "Quarter": "Q1", "Year": 2022, "Sales": 57000},
    {"ID": 5, "City": "Pittsburgh", "Region": "East", "Quarter": "Q1", "Year": 2022, "Sales": 53000},
    {"ID": 6, "City": "Atlanta", "Region": "South", "Quarter": "Q2", "Year": 2022, "Sales": 71000},
    {"ID": 7, "City": "Los Angeles", "Region": "West", "Quarter": "Q2", "Year": 2022, "Sales": 73000},
    {"ID": 8, "City": "Dallas", "Region": "South", "Quarter": "Q2", "Year": 2022, "Sales": 66000},
    {"ID": 9, "City": "New York", "Region": "East", "Quarter": "Q2", "Year": 2022, "Sales": 48000},
    {"ID": 10, "City": "Pittsburgh", "Region": "East", "Quarter": "Q2", "Year": 2022, "Sales": 36000}
  ]
}

The "data" array contains 10 JSON objects, each containing six key–value pairs.

Suppose you want to find which City achieved the highest Sales total.

The best solution is to use groupByJsonObject. The ATL is:

[[groupByJsonObject(WholeJSON.data, 'City', 'Sales', sum())]]

This tells Studio to analyze the whole "data" array, group all objects with matching City values, and aggregate the Sales values for each group. The final parameter input — sum() — specifies sum aggregation.

The output is this list of lists:

There are five inner lists — one for each group — and each contains two values: the groups City value and its sum-aggregated Sales value. The results are sorted by the aggregated values in descending order.

The printed result is:

Here's how you might turn the result into coherent narrative text:

[[

allResults = groupByJsonObject(WholeJSON.data, 'City', 'Sales', sum())

topResult = allResults[0:1]

topCityName = topResult[0][0]

topCitySales = abbreviateNumber(currencyFormat(topResult[0][1]))

joinStrings(topCityName, ' achieved the best sales, with a total of ', topCitySales, '.')

]]

The printed result is:

This example has one grouping field (City), one aggregation field (Sales), and one aggregation type (sum). The groupByJsonObject function can perform more complex analyses — see the function topic for examples.

Assume a "Describe a JSON Object" project with this data:

{
    "data": [
	[1, "Atlanta", "South", "Q1", 2022, 56000],
	[2, "Los Angeles", "West", "Q1", 2022, 52000],
	[3, "Dallas", "South", "Q1", 2022, 44000],
	[4, "New York", "East", "Q1", 2022, 57000],
	[5, "Pittsburgh", "East", "Q1", 2022, 53000],
	[6, "Atlanta", "South", "Q2", 2022, 71000],
	[7, "Los Angeles", "West", "Q2", 2022, 73000],
	[8, "Dallas", "South", "Q2", 2022, 66000],
	[9, "New York", "East", "Q2", 2022, 48000],
	[10, "Pittsburgh", "East", "Q2", 2022, 36000]
    ]
}

The "data" array contains 10 inner arrays, each containing six values. The values are indexed 0 to 5. The ID value is at index 0, the City value is at index 1, and so on. You must use the index numbers for reference.

Suppose you want to report on the top three cities for average sales.

The best solution is to use groupByJson. The ATL is:

[[groupByJson(WholeJSON.data, 1, 5, mean())]]

This tells Studio to analyze the whole "data" array, group the arrays with matching City values (Index 1), and aggregate the Sales values (Index 5) for each group. The final parameter input — mean() — specifies mean aggregation.

The result is this list of lists:

There are five inner lists — one for each group — and each contains two values: the groups City value and its mean-aggregated Sales value. The results are sorted by the aggregated values in descending order.

The printed result is:

Here's how you might turn the result into coherent narrative text:

[[

allResults = groupByJson(WholeJSON.salesData, 1, 5, mean())

topResults = allResults[0:3]

topThreeCities = map(topResults, x -> x[0])

topThreeSales = map(topResults, x -> abbreviateNumber(currencyFormat(x[1])))

"The top three cities for average sales were [[topThreeCities]], with [[topThreeSales]] respectively."

]]

The printed result is:

The groupByJson function can perform more complex analyses — see the function topic for examples.