Sorting column in ASCII mode

[#1]

I have a problem with sort order in QlikView. I import data from an iSerie with ODBC and i have a lot of field containing Alphanumeric value . (mixed of Numbers and letters. ex : 145ABC14). When i create a table with this fields and click on the title to sort the column (The column are not sorted in ascii order but in a special QlikView order.

This is a big problem for me because when i compare a list of value with my system information. Fields are not in the same place in the list.

It seems that there are no way to sort all the column in ascii mode. 😞
Is there somebody who have solved this problem ?

Thanks

Here is the explanation of QlikView :

Sorting in QlikView is done character by character (left to right) according to national collation. This is not strictly the same as in Microsoft products where sorting is performed by string.

This means that only alphanumeric characters are of interest in regards of sort-order. All non-alphanumeric characters are treated the same, on the same level. For example:

"A-B" will be sorted before "A BA" because "-" and " " will be treated equally and more characters are required to separate the strings.

If the string contains numeric values, QlikView will first try to sort the numeric part of the string, before applying alphanumeric sorting. This will explain why 1ABAC2244 will be sorted before 002500A76 in the following example. The numeric part of 002500A76 will be 002500 (or 2500) which will be compared with 1 (numeric part of 1ABAC2244).
All identical numeric values receive the same symbol regardless of their presentation - i.e. 0025, 025, 25 etc. are considered identical and the symbol used to represent these is simply the one that appears first in the array ("0025" in this case) and this implies that these values are grouped together, sharing a common underlying symbol…


This is a somewhat simple sorting strategy, but with great advantage in performance compared to Microsofts.

[#2]

Well this don't work on my reel data. ;-(

I think it is my conversion formula. I don't remenber how to convert a chain in decimal caracter by caracter

ord(mid(TEMP1,1)) * 1000000000000 +
ord(mid(TEMP1,2)) * 100000000000 +
ord(mid(TEMP1,3)) * 10000000000 +
ord(mid(TEMP1,4)) * 1000000000 +
ord(mid(TEMP1,5)) * 100000000 +
ord(mid(TEMP1,6)) * 10000000 +
ord(mid(TEMP1,7)) * 1000000 +
ord(mid(TEMP1,8)) * 100000 +
ord(mid(TEMP1,9)) * 10000 +
ord(mid(TEMP1,10))* 1000 +
ord(mid(TEMP1,11))* 100 +
ord(mid(TEMP1,12))* 10 +
ord(mid(TEMP1,13))* 1) as TEST1,

[#3]

Works just fine:

[#4]

In my exemple yes, but with a large panel of code (Near 90000) this don't work. [:'(]

[#5]

I try this one but i don't have enough precision in Qlikview. The result is to big. 😞

I am going to test John solution with join.

dual(Text(NICART),
ord(mid(NICART,1)) * pow(256,12) +
ord(mid(NICART,2)) * pow(256,11) +
ord(mid(NICART,3)) * pow(256,10) +
ord(mid(NICART,4)) * pow(256,9) +
ord(mid(NICART,5)) * pow(256,8) +
ord(mid(NICART,6)) * pow(256,7) +
ord(mid(NICART,7)) * pow(256,6) +
ord(mid(NICART,8)) * pow(256,5) +
ord(mid(NICART,9)) * pow(256,4) +
ord(mid(NICART,10)) * pow(256,3) +
ord(mid(NICART,11)) * pow(256,2) +
ord(mid(NICART,12)) * pow(256,1) +
ord(mid(NICART,13)) * pow(256,0)) as [TEST2],

Try a macro too. Don't work

Dual(Text(NICART),CvtASCII(NICART) as TEST

Function CvtASCII (txt)
t = 0
e = len(txt)-1

For i = 1 to len(txt)
t = t + asc(mid(txt,i,1)) * (256^e)
e = e - 1
next

CvtASCII = t
End function

[#6]

Here is an example with new code. '24310031599' is not sorted

Just adds these 3 lines

000YA2301,000YA2301
1B4A00ENGIN,1B4A00ENGIN
01L27300,01L27300

[johnw]

Looks like you figured out the powers of 10 thing. It doesn't work because you have 256 values for ord() assuming you're sticking with the first 256 standard ASCII characters. That's why I was using powers of 256. The parentheses I was using meant I had powers of 256 just like your new version with pow(256,...). I suspect that my version would be slightly faster to calculate, but I like your version better because it seems more clear.

Unfortunately, yeah, QlikView doesn't have enough precision for this to work on that many characters. It might work MOST of the time, but probably wouldn't work on text that is 13 bytes long and only different on the last character, for instance.

So perhaps we need to turn it into a string instead, a purely numeric string, and have it as a separate field, and sort by that field in a sort expression:

num(ord(mid(NICART,1)),'000')
&num(ord(mid(NICART,2)),'000')
&num(ord(mid(NICART,3)),'000')
... as NICART_SORT

Or something along those lines. Boy, it sure seems like this should be simpler, doesn't it? Surely we're missing something.

[#8]

Try this one too. Don't seems to work.
I don't know if i missed something. But it seems to be impossible to use ASCII sort in QlikView !!!

1) If i sort only the first 4 caracters in sort expression it's ok. but the sort must be on 13 caracters (Total 12 caracters of precision)

= num(ord(mid(TEST1, 1)) ,000) &
num(ord(mid(TEST1, 2)) ,000) &
num(ord(mid(TEST1, 3)) ,000) &
num(ord(mid(TEST1, 4)) ,000)

2) If i sort only the first 5 caracters in sort expression. Not Ok (Total 15 caracters of Precision)

= num(ord(mid(TEST1, 1)) ,000) &
num(ord(mid(TEST1, 2)) ,000) &
num(ord(mid(TEST1, 3)) ,000) &
num(ord(mid(TEST1, 4)) ,000) &
num(ord(mid(TEST1, 5)) ,000)

3) In the script. Too many precision. Not Ok

[Final Table]:
LOAD
num(ord(mid(TEST1, 1)) ,000) &
num(ord(mid(TEST1, 2)) ,000) &
num(ord(mid(TEST1, 3)) ,000) &
num(ord(mid(TEST1, 4)) ,000) &
num(ord(mid(TEST1, 5)) ,000) &
num(ord(mid(TEST1, 6)) ,000) &
num(ord(mid(TEST1, 7)) ,000) &
num(ord(mid(TEST1, 8)) ,000) &
num(ord(mid(TEST1, 9)) ,000) &
num(ord(mid(TEST1,10)) ,000) &
num(ord(mid(TEST1,11)) ,000) &
num(ord(mid(TEST1,12)) ,000) &
num(ord(mid(TEST1,13)) ,000) as TEST1_ASCII,

num(ord(mid(TEST2, 1)) ,000) &
num(ord(mid(TEST2, 2)) ,000) &
num(ord(mid(TEST2, 3)) ,000) &
num(ord(mid(TEST2, 4)) ,000) &
num(ord(mid(TEST2, 5)) ,000) &
num(ord(mid(TEST2, 6)) ,000) &
num(ord(mid(TEST2, 7)) ,000) &
num(ord(mid(TEST2, 8)) ,000) &
num(ord(mid(TEST2, 9)) ,000) &
num(ord(mid(TEST2,10)) ,000) &
num(ord(mid(TEST2,11)) ,000) &
num(ord(mid(TEST2,12)) ,000) &
num(ord(mid(TEST2,13)) ,000) as TEST2_ASCII,

*;
LOAD
text(TEST1) as TEST1,
text(TEST2) as TEST2,
text(TEST3) as TEST3;
SQL SELECT
TEST1,
TEST2,
TEST3
FROM QVTEST;

STORE [Final Table] INTO PB_TRI9.qvd;
DROP TABLE [Final Table];

[Final Table QVD]:
LOAD * FROM PB_TRI9.qvd (qvd);

[johnw]

Hmmmm, yeah, doesn't work. I don't know why.

Is it just me, or do sort expressions pretty much NEVER work? They've failed to work for me so often I don't even TRY using them any more. I ALWAYS try to find another way. Am I just missing something basic about how they're supposed to work??? *grumble*