Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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?