topic Re: Sort order issue in App Development

Sort order issue

shope99 — Fri, 05 Apr 2019 04:02:14 GMT

I have a set of "account numbers" which are alphanumeric. They can vary from purely numbers (48320010), to "number-like" (2-22-122-120322) to "alphanumeric" (ABC123). We'd like to allow users to sort these account numbers, and given the variability in them, alphanumeric sorting makes the most sense.

I have set the sorting in a table to "Custom", unchecked "Sort Numerically", and checked "Sort Alphabetically". I still get a sort order which seems to be ordering somewhat numerically. I've tried wrapping the field in "TEXT" to force it to be text, still no luck.

For the table

LOAD * INLINE [
AccountName2
ABC123
2-22-123-5131
02-4534222-5000283 0
0005 06020
58455315
];

I would expect the order to be exactly what I would get passing them through the Unix or Windows sort function:

0005 06020
02-4534222-5000283 0
2-22-123-5131
58455315
ABC123

But, in Qlik, I get the following order for TEXT(AccountName), sorted Alphabetically, Ascending:

2-22-123-5131
02-4534222-5000283 0
0005 06020
58455315
ABC123

The problem gets very, very strange looking when we are dealing with 100's of account numbers.

Any suggestions? I understand it is an issue with Qlik's internal representation of numbers versus strings, and that somehow in this column items that are numeric or number-like are treated differently than "text" columns. But, even if I force the numbers (in the load script!) to all start with "A", the sort order still seems to have an element of "numberness" to it:

A2-22-123-5131
A02-4534222-5000283 0
A0005 06020
A58455315
ABC123

I have spent far too many hours on what I thought would be a simple issue to fix. Any suggestions on what next to try??

QVF with examples attached.

Re: Sort order issue

Vegar — Fri, 05 Apr 2019 04:15:50 GMT

Have you tried to sort your account table at script level and use the load order sort in your application?

LOAD text(AccountName2 ) as AccountName2 INLINE [
AccountName2
ABC123
2-22-123-5131
02-4534222-5000283 0
0005 06020
58455315
]

Order by AccountName2;

Re: Sort order issue

shope99 — Fri, 05 Apr 2019 04:26:58 GMT

Thanks for the quick reply! I couldn't get the order by to work on the inline, but in the same spirit I tried
[Accounts3]:
LOAD TEXT(AccountName) as AccountName3
RESIDENT Accounts
Order By AccountName;

And, then I removed all sort options ("custom" -> unchecked both sort numerically and alphabetically). The chart properties pane even says "Currently Sorting on Load Order". Here's what that changed to:

0005 06020
58455315
2-22-123-5131
02-4534222-5000283 0
ABC123

Which, in some ways, is even more bewildering.

Re: Sort order issue

Vegar — Fri, 05 Apr 2019 04:33:50 GMT

Maybe there is a glitch in how the QIX engine handle sorting for the moment? I experienced issues with alphabetical sorting of dual values a couple of days ago.

How to sort a listbox with dual values alphabetical?