Sorting

You can sort any list with the sort function, but for sorting lists of numbers or strings, you have better options.

For numbers, use sortNumbers or sortNumbersReverse.

[[

myList = makeList(10, 40, 30, 20, 50) 

sortNumbers(myList) 

]]

[[

myList = makeList(10, 40, 30, 20, 50)

sortNumbersReverse(myList) 

]]

For strings, use sortStrings or sortStringsReverse.

[[ 

myList = makeList('John', 'Paul', 'George')

sortStrings(myList) 

]]

[[ 

myList = makeList('John', 'Paul', 'George')

sortStringsReverse(myList) 

]]

A common task is to sort a table region by column values.

For single-column sorts, use one of these functions:

A JSON array might be an array of objects or a two-dimensional array (array of arrays). In either case, an array is a list-like structure that you can reorder by using the sort function. We have organized this section as follows:

{
    "data": [
        {"city": "Boston", "sales": 56000, "target": 50000},
	{"city": "Dallas", "sales": 42000, "target": 38000},
	{"city": "Austin", "sales": 47000, "target": 40000},
	{"city": "Denver", "sales": 57000, "target": 60000}
    ]
}

[[sort(WholeJSON.data, (x,y) -> sign(y.sales - x.sales))]]

{
    "data": [
        {"city": "Denver", "sales": 57000, "target": 60000},
        {"city": "Boston", "sales": 56000, "target": 50000},
        {"city": "Austin", "sales": 47000, "target": 40000},	
        {"city": "Dallas", "sales": 42000, "target": 38000}
    ]
}

[[sort(WholeJSON.data, (x,y) -> sign(diff(y.sales,y.target) - diff(x.sales,x.target)))]]

{
    "data": [
        {"city": "Austin", "sales": 47000, "target": 40000},        
        {"city": "Boston", "sales": 56000, "target": 50000},	
        {"city": "Dallas", "sales": 42000, "target": 38000},
        {"city": "Denver", "sales": 57000, "target": 60000}
    ]
}

{
    "data": [
        ["Boston", 56000, 50000],
	["Dallas", 42000, 38000],
	["Austin", 47000, 40000],
	["Denver", 57000, 60000]
    ]
}

[[sort(WholeJSON.data, (x,y) -> sign(y[1] - x[1]))]]

{
    "data": [
	["Denver", 57000, 60000],
        ["Boston", 56000, 50000],
        ["Austin", 47000, 40000],
	["Dallas", 42000, 38000]
    ]
}

[[sort(WholeJSON.data, (x,y) -> sign(diff(y[1],y[2]) - diff(x[1],x[2])))]]

{
    "data": [
        ["Austin", 47000, 40000],
        ["Boston", 56000, 50000],
	["Dallas", 42000, 38000],
	["Denver", 57000, 60000]
    ]
}

Some groupBy functions — such as groupByTable and groupByJson — return a list of lists. These functions present the list already sorted, but you may wish to sort it differently. You can do this with sort.

Assume that [[groupByTable(WholeTable, Region, (Sales, Target))]] returns this result:

In printed form:

There are three inner lists, each containing three values: (1) the group's Region value, (2) the group's sum-aggregated Sales total, and (3) the group's sum-aggregated Target total.

The result is automatically sorted in descending order, using the first aggregated value (Sales). But suppose you want a different sort order. Suppose you want to identify the top-performing region and measure performance as Sales minus Target. You can do this with the sort function.

[[

results = groupByTable(WholeTable, Region, (Sales, Target))

sort(results, (x,y) -> sign(diff(y[1],y[2]) - diff(x[1],x[2])))

]]

The result is this list of lists:

Now the inner list for the West region comes first, as its Sales minus Target value (50,000) is highest.

Some ATL functions — such as anomalyDetectorOutliers and rollingCorrelation — return a list of ATL objects. It's also common to write analytics functions that return this kind of list.

For this example:

[[ global.results = anomalyDetectorOutliers(Orders, Date) ]]

Assume the above returns this list is returned:

The list contains three ATL objects. Each object is a list of key–value pairs. Similar to when sorting an array of JSON objects, you can sort a list of ATL objects by referencing key names. For this to work, the referenced key name(s) must appear in each ATL object.

The list above is already sorted by the value field in descending order, but suppose you want to sort the list by the label field in chronological order (earliest to latest). You can do this with the sort function:

[[ sort(results, (x,y) -> y.label < x.label ? 1 : (y.label == x.label ? 0 : -1)) ]]

The sorted list is:

The object for the 2022-04-15 anomaly is now first because it's the earliest.

Note that the date values of 'label' in this case are actually strings, and they are sorted as strings (i.e. alphanumerically). Because they are in the format yyyy-MM-dd the sort function will sort them in the correct date order. However, if they were in the format dd-MM-yyyy, then a string sort would not work as expected.

The sorted result would be in ascending alphanumerical order, with 08-08-2022 listed first:

In such a case, you can convert the values to dates within the comparator function of the sort, as shown below, and they will be sorted as dates.

[[ sort(results, (x,y) -> parseDateTime(y.label, "dd-MM-yyyy") < parseDateTime(x.label, "dd-MM-yyyy") ? 1 : (parseDateTime(y.label, "dd-MM-yyyy") == parseDateTime(x.label, "dd-MM-yyyy") ? 0 : -1)) ]]

The sorted result lists the object for the 15-04-2022 anomaly first again: