Text Sort Issue

[gfarrell]

It seems strange that I haven't seen this before but i am not getting the expected results from a text sort against a basic code value. I can recreate this by loading a simple two character code.

SortTest:

Load text(Code);

Load * INLINE [

Code

1a

1b

2a

10

20

30

10a

];

The sort order in qlikview when using a text sort option is ;

1a,1b,2a,10,10a,20,30

The expected sort order is;

10,10a,1a,1b,20,2a,30

Apart from using Load order order using dual to assign a sequence I would think this text sequence would follow standard conventions.

Thanks in advance.

[johnw]

Numbers before letters and letters before numbers are both standard conventions for text sorting. EBCDIC, for instance, puts letters before numbers. So does QlikView.

However, I suspect you could actually report this as a bug. The reason is that the help text says this about sorting in text order:

"Text Toggles the status whether the sorting procedure of values should be made in alphabetical order according to the ASCII standard."

In ASCII, numbers occur before letters. Therefore, numbers should sort first. Since they don't, QlikView is not working as stated. Now, quite possibly many people have already come to depend on the existing sort order, so it probably won't be changed. They'd probably just change the help text. But in any case, SOMETHING appears to be wrong.

As far as working around it, since ord() returns the ASCII number for a character, you can use it to handle TRUE ASCII sorting instead of whatever QlikView is doing. You can use ord() in a sort expression, to make a sort field, or with dual(). See attached for all three.

[gfarrell]

John,

Thanks for the quick response and the confirmation that I am not crazy. Also thanks for the creative option using ord(). I will have to keep an eye on these text sorts in the future.

[IAMDV]

John - Clever workaround. Thanks for sharing this...

Cheers - DV