sort

Sorts a list or list-like object such as a JSON array. You can define the sort criteria by using a predefined comparator function, or you can write your own lambda expression. The result is a sorted list.

Studio has other sorting functions, many of which take fewer parameters and don't require a lambda expression. These are better for simple tasks such as sorting a list of strings alphabetically or sorting a list of numbers in ascending order. For a full list of sorting functions, see the Advanced functions index.

To sort a table by the values in one column — e.g. sort by the values in the Sales column — use a sortBy function such as sortByColumnNumbers or sortByColumnNumbersReverse.

The sort function is best when your data is a list or array of objects that you wish to sort by comparing values from one or more fields. See the examples for further guidance.

Parameters

INPUT (list, column, row, or JSON array)
A list or list-like object to sort.
Note: A list-like object = a table column, table row, or JSON array.
COMPARATOR FUNCTION (function)
A comparator function that defines how to sort the values.
Note: You can use a predefined comparator function or a lambda expression.

Comparator functions

Name	Description	Equivalent Lambda Expression
`numericSort()`	Sorts numbers in ascending order.	`(x,y) -> sign(x - y)`
`numericSortReverse()`	Sorts numbers in descending order.	`(x,y) -> sign(y - x)`
`stringSort()`	Sorts strings in alphabetical order.	`(a,b) -> b < a ? 1 : (b == a ? 0 : -1)`
`stringSortReverse()`	Sorts strings in reverse-alphabetical order.	`(a,b) -> b > a ? 1 : (b == a ? 0 : -1)`

Use the predefined comparator functions for simple tasks such as those described above. The equivalent lambda expressions work equally well, but the predefined functions allow you to write simpler ATL.

Note that a comparator function written as a lambda must:

take two parameters; and
return a value of 1, -1, or 0.

In the example (x,y) -> sign(x - y), the parameter list is (x,y) and the function body is sign(x - y). When applying the function body to the parameters (x,y) returns 1, the values are swapped so that y comes before x in the sorted list. If the result is -1, the parameters values are not swapped. Similarly, if the result is 0, the parameter values are not swapped, as they are considered equal.

Tip

You must use a lambda expression for more complex sorting tasks — for example, to sort an array or list of objects by comparing values from one or more fields.

Examples

ATL in Script	Result
`[[sort((2,1,3), numericSort())]]`	1, 2 and 3
`[[sort((2,1,3), (x,y) -> sign(x - y))]]`	1, 2 and 3
`[[sort((2,1,3), numericSortReverse())]]`	3, 2 and 1
`[[sort((2,1,3), (x,y) -> sign(y - x))]]`	3, 2 and 1
`[[sort(('y','z','x'), stringSort())]]`	x, y and z
`[[sort(('y','z','x'), (a,b) -> b < a ? 1 : (b == a ? 0 : -1))]]`	x, y and z
`[[sort(('y','z','x'), stringSortReverse())]]`	z, y and x
`[[sort(('y','z','x'), (a,b) -> b > a ? 1 : (b == a ? 0 : -1))]]`	z, y and x

Tip

You can often perform simple sorting tasks like these with other ATL functions. For example, [[sortNumbers((2,1,3))]] does the same as [[sort((2,1,3), numericSort())]]. Refer to Advanced functions for a full list of sorting functions.

Each example above uses a hard-coded list for the input. We've included more realistic examples below.

Sorting a JSON array

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

{
    "finances": [
        {
            "branch": "Boston",
            "sales": 329493.49,
            "target": 300000
        },
        {
            "branch": "Chicago",
            "sales": 463603.17,
            "target": 400000
        },
        {
            "branch": "Los Angeles",
            "sales": 478745.37,
            "target": 500000
        },
        {
            "branch": "New York",
            "sales": 467359.45,
            "target": 450000
        }
    ]
}

The "finances" array holds four JSON objects, each containing three key–value pairs. Suppose that you want to sort the array by sales in descending order. This requires a lambda expression in the second parameter.

The ATL [[sort(WholeJSON.finances, (x,y) -> sign(y.sales - x.sales))]] returns this list of objects:

You could then use the sorted list to produce narrative text.

[[

sortBySales = sort(WholeJSON.finances, (x,y) -> sign(y.sales - x.sales))

topSeller = sortBySales[0:1]

"The top-selling branch is [[map(topSeller, x -> x.branch)]], with sales of [[map(topSeller, x -> currencyFormat(x.sales))]]."

]]

The output text is:

The top-selling branch is Los Angeles, with sales of $478,745.37.

Sorting a list of ATL objects

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

	Branch	Sales	Target
Row 1	Boston	329493.49	300000
Row 2	Chicago	463603.17	400000
Row 3	Los Angeles	478745.37	500000
Row 4	New York	467359.45	450000

If you want to sort the table by the values in one column, you can do this easily with another function such as sortByColumnNumbers or sortByColumnStrings. The sort function is required when you want to sort by using values in multiple columns.

For example, suppose you want to identify the top-performing branch when analyzing Sales against Target in terms of percentage change. To do this, you need to reorganize the table as a list of ATL objects, then sort the list by using the sort function.

The following ATL creates a variable named data.

[[data = map(rowNames(), x -> createAtlObject(('branch','sales','target'), (x, value(cell(x,Sales)), value(cell(x,Target)))))]]

The variable returns this list of ATL objects:

( ( branch = Boston, sales = 329493.49, target = 300000 ), ( branch = Chicago, sales = 463603.17, target = 400000 ), ( branch = Los Angeles, sales = 478745.37, target = 500000 ), ( branch = New York, sales = 467359.45, target = 450000 ) )

You can then use sort on the variable.

[[

data = map(rowNames(), x -> createAtlObject(('branch','sales','target'), (x, value(cell(x,Sales)), value(cell(x,Target)))))

sort(data, (x,y) -> sign(percentageChange(y.sales,y.target) - percentageChange(x.sales,x.target)))

]]

The result is an amended list with the objects sorted by performance.

( ( branch = Chicago, sales = 463603.17, target = 400000 ), ( branch = Boston, sales = 329493.49, target = 300000 ), ( branch = New York, sales = 467359.45, target = 450000 ), ( branch = Los Angeles, sales = 478745.37, target = 500000 ) )

You can then use the sorted list to produce narrative text.

[[

data = map(rowNames(), x -> createAtlObject(('branch','sales','target'), (x, value(cell(x,Sales)), value(cell(x,Target)))))

sortedData = sort(data, (x,y) -> sign(percentageChange(y.sales,y.target) - percentageChange(x.sales,x.target)))

topPerformerName = map(sortedData[0:1], x -> x.branch)

topPerformerValue = map(sortedData[0:1], x -> percentageChange(x.sales, x.target))

perfDirection = topPerformerValue > 0 ? 'above' : 'below'

"The top-performing branch was [[topPerformerName]], with sales [[abs(topPerformerValue)]]% [[perfDirection]] target."

]]