topic Sorting Alphanumeric values in QlikView

Sorting Alphanumeric values

markmccoid — Wed, 15 Apr 2009 21:30:24 GMT

Hi,

I loaded the following in an inline table:


hold:
LOAD * INLINE [
 F1
 200240AO
 500072CT
 BRIDESCT
 200270AO
 WS1000CT
 WS1000RZ
 8CS077CT
];<div>

Once loaded I create a list box and set the sort to Text ascending and get the following:

8CS077CT

200240AO

200270AO

500072CT

BRIDESCT

WS1000CT

WS1000RZ

If I change my listbox to use and expression --> Text(F1), then the sort is done properly:

200240AO

200270AO

500072CT

8CS077CT

BRIDESCT

WS1000CT

WS1000RZ

Trying to figure out what QV is thinking. I even tried the following code:

----------------------

Text:

LOAD

Text(F1) as TextField

RESIDENT hold;

-------------------------

But it still sorted as follows:

8CS077CT

200240AO

200270AO

500072CT

BRIDESCT

WS1000CT

WS1000RZ

Thanks for the help.

Mark

Sorting Alphanumeric values

rwunderlich — Thu, 16 Apr 2009 07:02:00 GMT

That's an odd one all right. I note that even using an expression like

=F1

gives the expected sort order.

I think I see a pattern to the sort. The values that start with numbers get stripped starting at the first alpha and the resulting number string is sorted using string sorting rules. So what is being sorted is:

200240

200270

500072

BRIDESCT

WS1000CT

WS1000RZ

You can play with values like 5Z, 50Z & 80Z and see the pattern easier. Like your test, making another variable in the load -- even 'A' & F1 -- doesn't seem to fix it. But using that same expression (any expression) in a listbox gives the correct sort.

I would inquire with Support on this one. Let me know what you find out.

-Rob

Sorting Alphanumeric values

Oleg_Troyansky — Thu, 16 Apr 2009 07:15:41 GMT

Rob,

you are absolutely right. I vaguely remember reading an announcement that QlikView is adopting "natural sorting" for more logical sorting of mixed strings. For example, the following values:

A1B

A2Z

A9K

A11

A19

will be sorted in this exact order, even though regular "alphabetical sort order" would place 11 before 9...

Oleg

Sorting Alphanumeric values

rwunderlich — Thu, 16 Apr 2009 07:58:49 GMT

Oleg,

Thanks for the clarification. Looks like the workaround is to use an expression if we want the "ASCIIbetical" sort. This seems to work for chart dimensions as well.

Here's a blog post that explains a "Natural Sorting" algorithm.

http://www.davekoelle.com/alphanum.html

-Rob