Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
shope99
Partner - Contributor II
Partner - Contributor II

Sort order issue

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.

 

 

3 Replies
Vegar
MVP
MVP

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;

shope99
Partner - Contributor II
Partner - Contributor II
Author

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.
Vegar
MVP
MVP

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?