topic Results in query not sorting properly. Sorting by numeric field, but reading like text and jumping from 10 to 100 in ArcGIS Online Questions

Results in query not sorting properly. Sorting by numeric field, but reading like text and jumping from 10 to 100

SimonCrutchley — Tue, 08 Dec 2020 11:37:17 GMT

I'm running an app built in WebApp Builder to display archaeological reports. I've built a query to allow searching by date, author etc as well as the map search. The query is supposed to sort first by date (formatted as a number it's only the year that matters) and then by report number (also formatted as a number). However, when running the query it sorts the output by date, but then seems to count the Number as text because it counts 1,2,3,4,5,6,10,100,101-109,11,110-119,12 etc. Has anyone come across such an issue before, and have you any idea how to rectify it?

I could reformat the field as text, adding leading zeros, but that seems counter intuitive, when I have a numeric field

Thanks

Re: Results in query not sorting properly. Sorting by numeric field, but reading like text and jumping from 10 to 100

DanPatterson — Tue, 08 Dec 2020 11:55:56 GMT

You want what is called a "natural sort"

I can only demonstrate using python...

a = ['1', '21', '3', 'r4', 'r41', '7', '71', 'r711', '10', 'r100'] b = sorted(a) print("input - \n{}".format(a)) print("text sort - \n{}".format(b)) vals = natsort(a) print("natural sort - \n{}".format(vals)) input - ['1', '21', '3', 'r4', 'r41', '7', '71', 'r711', '10', 'r100'] text sort - ['1', '10', '21', '3', '7', '71', 'r100', 'r4', 'r41', 'r711'] natural sort - ['1', '3', '7', '10', '21', '71', 'r4', 'r41', 'r100', 'r711']

The process and code

import re def natsort(lst): """natural sort""" import re convert = lambda text: int(text) if text.isdigit() else text a_key = lambda key: [convert(c) for c in re.split('([0-9]+)', key)] return sorted(lst, key=a_key)

Re: Results in query not sorting properly. Sorting by numeric field, but reading like text and jumping from 10 to 100

SimonCrutchley — Tue, 08 Dec 2020 12:17:46 GMT

Hi Dan,

Thanks for that, but I still don't understand why it isn't just sorting 'naturally' anyway. The field is numeric, so surely it should sort 1,2,3,4,5,6,7,8,9,10,11,12 etc.

I'm afraid I don't understand the code etc.

Sorry

Re: Results in query not sorting properly. Sorting by numeric field, but reading like text and jumping from 10 to 100

DanPatterson — Tue, 08 Dec 2020 12:25:26 GMT

It would sort in numeric order if it were numeric. If it is sorted as you show, it is text and to get it to sort as a number, it has to be converted from text to number. A number can look like a number, but you may not see what makes it appear as text.

The code sample is what could be used for field calculations in tables in ArcGIS Pro, for instance

Re: Results in query not sorting properly. Sorting by numeric field, but reading like text and jumping from 10 to 100

SimonCrutchley — Tue, 08 Dec 2020 12:48:48 GMT

But it is numeric, at least according to the field data (see attached). That's what I don't understand.

Re: Results in query not sorting properly. Sorting by numeric field, but reading like text and jumping from 10 to 100

DanPatterson — Tue, 08 Dec 2020 14:22:12 GMT

apparently the combination of date formatted as a number and a number doesn't yield a sortable number.

Re: Results in query not sorting properly. Sorting by numeric field, but reading like text and jumping from 10 to 100

SimonCrutchley — Tue, 08 Dec 2020 14:25:26 GMT

In the query, the date is all the same in all the examples I've tried; it's the No field, which is just a number, that fails to sort.