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]

You can load data in appropriate order by using ORDER BY in your ODBC SQL sentence. After that you can sort data by "Load Order" in QV.

[#3]

I know this solution but it works for only one field (The first in order by). As you can see in my exemple i have 2 fields or more to sort in ascii mode. So this solution don't solve the problem completly. I want all fields to be sorted in ascii.

Thanks

[johnw]

Well, this is a BAD solution, but if nobody comes up with something better...

[TEST1 Sort Order]:
LOAD TEST1
;
SQL SELECT DISTINCT TEST1
FROM...
ORDER BY TEST1
;
[TEST2 Sort Order]:
LOAD TEST2
;
SQL SELECT DISTINCT TEST2
FROM...
ORDER BY TEST2
;
[Real Table]:
LOAD ...
;
SQL SELECT ...
FROM ...
;
DROP TABLES
[TEST1 Sort Order]
,[TEST2 Sort Order]
;

[#5]

Hi john,

Thanks for your response. Effectively i don' t have any other suggestion, so il will test it asap.

Do you have the problem too ? I don't anderstand why QlikView don't solve this ? (Just a little chekbox to sort in ascii mode for each field)

Seems to be alone, to want to sort field in ascii mode like other system information !

Thanks for sharing

[#6]

Hi John,

Thanks for the workaround. It works except that letters are before numbers (But it'is like Excel, so ...)

I test a second part (Store the final table in a QVD) but the ASCII sort is lost. (so I have to reload the field sort just before the QVD load)

Here is the final script :

// Part 1 - ASCII SORT => Ok
[Test1 Sort Order]:
LOAD
Text(TEST1) as TEST1;
SQL SELECT DISTINCT TEST1
FROM QVTEST ORDER BY TEST1;

[Test2 Sort Order]:
LOAD
Text(TEST2) as TEST2;
SQL SELECT DISTINCT TEST2
FROM QVTEST ORDER BY TEST2;

[Final Table]:
LOAD
Text(TEST1) as TEST1,
Text(TEST2) as TEST2,
Text(TEST3) as TEST3;
SQL SELECT
TEST1,
TEST2,
TEST3
FROM QVTEST;

DROP TABLE [Test1 Sort Order];
DROP TABLE [Test2 Sort Order];

exit script;

// Part2 - ASCII SORT with STORE => ASCII Sort is lost
STORE [Final Table] INTO PB_TRI5.qvd;
DROP TABLE [Final Table];

[Final Table QVD]:
LOAD
TEST1,
TEST2
FROM PB_TRI5.qvd (qvd)

Nobody see an inconvenience, that QlikView don't sort column in ASCII Mode ?

[johnw]

To preserve the sort order when storing in a QVD, you could use the dual() function, and then sort in numeric order. I think like this:

[Final Table]:
LOAD
Text(TEST1) as TEMP1,
Text(TEST2) as TEMP2,
Text(TEST3) as TEST3;
SQL SELECT
TEST1,
TEST2,
TEST3
FROM QVTEST;

LEFT JOIN ([Final Table])
LOAD
Text(TEST1) as TEMP1,
dual(Text(TEST1),recno()) as TEST1;
SQL SELECT DISTINCT TEST1
FROM QVTEST ORDER BY TEST1;

LEFT JOIN ([Final Table])
LOAD
Text(TEST2) as TEMP2,
dual(Text(TEST2),recno()) as TEST2;
SQL SELECT DISTINCT TEST2
FROM QVTEST ORDER BY TEST2;

DROP FIELDS
TEMP1,
TEMP2;

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

[Final Table QVD]:
LOAD
TEST1,
TEST2
FROM PB_TRI5.qvd (qvd);

And yeah, I've never had a need to do an ASCII sort specifically, though it is surprising that it isn't supported. There might well be an easy solution, and I just don't know it because I've never needed it. Perhaps something to do with code pages? I'm not sure.

[rwunderlich]

Here's another workaround that doesn't require any script work. I got your sample charts to sort correctly by selecting Sort by Text and Sort by Expression:

=ord(TEST1) & ord(mid(TEST1,2)) & ord(mid(TEST1,3))

I actually only needed to use the first two chars to get your sample to sort the way you wanted. How many chars you have to sample is dependent on your data pattern. i don't know how practical or performant this would be with your real data.

-Rob

[johnw]

Ah, good thinking. And if it doesn't perform well enough to be used in a chart, I suspect it would perform well enough to be used during the script. Maybe something like this:

[Final Table]:
LOAD
dual(TEMP1,((...
ord(mid(TEMP1,1) *256
+ ord(mid(TEMP1,2))*256
+ ord(mid(TEMP1,3))*256
...) as TEST1
,dual(TEMP2,((...
ord(mid(TEMP2,1) *256
+ ord(mid(TEMP2,2))*256
+ ord(mid(TEMP2,3))*256
...) as TEST2
,TEST3
LOAD
Text(TEST1) as TEMP1,
Text(TEST2) as TEMP2,
Text(TEST3) as TEST3;
SQL SELECT
TEST1,
TEST2,
TEST3
FROM QVTEST;

[#10]

Here is the final script. It's a little complicated but it seems to work in my QVD. (My fields is 13 caracters long)

Thanks you very much Rob and John. I learn a lot reading your post.

[Final Table]:
LOAD
dual(TEMP1,
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,

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

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

DROP FIELDS TEMP1,TEMP2;

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

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