topic Sorting column in ASCII mode in QlikView

Sorting column in ASCII mode

Fri, 02 Jul 2010 12:30:07 GMT

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.

Sorting column in ASCII mode

Fri, 02 Jul 2010 13:31:20 GMT

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.

Re. :Re: Sorting column in ASCII mode

Fri, 02 Jul 2010 13:48:03 GMT

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

Re. :Re: Sorting column in ASCII mode

johnw — Fri, 02 Jul 2010 23:24:21 GMT

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]
;

Re. :Re: Re. :Re: Sorting column in ASCII mode

Sat, 03 Jul 2010 13:13:37 GMT

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

Re. :Re: Re. :Re: Sorting column in ASCII mode

Mon, 05 Jul 2010 08:18:38 GMT

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 ?

Re. :Re: Re. :Re: Sorting column in ASCII mode

johnw — Tue, 06 Jul 2010 17:49:28 GMT

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.

Re. :Re: Re. :Re: Sorting column in ASCII mode

rwunderlich — Wed, 07 Jul 2010 00:09:03 GMT

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

Re. :Re: Re. :Re: Sorting column in ASCII mode

johnw — Wed, 07 Jul 2010 00:24:22 GMT

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;

Re. :Re: Re. :Re: Re. :Re: Sorting column in ASCII mode

Wed, 07 Jul 2010 09:23:26 GMT

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);

Re. :Re: Re. :Re: Re. :Re: Sorting column in ASCII mode

Wed, 07 Jul 2010 13:51:44 GMT

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,

Re. :Re: Re. :Re: Re. :Re: Sorting column in ASCII mode

Wed, 07 Jul 2010 14:28:48 GMT

Works just fine:

Re. :Re: Re. :Re: Re. :Re: Re. :Re: Sorting column in ASCII mode

Wed, 07 Jul 2010 15:35:44 GMT

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

Re. :Re: Re. :Re: Re. :Re: Re. :Re: Sorting column in ASCII mode

Wed, 07 Jul 2010 15:45:30 GMT

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

Re. :Re: Re. :Re: Re. :Re: Re. :Re: Sorting column in ASCII mode

Wed, 07 Jul 2010 16:13:24 GMT

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

Just adds these 3 lines

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

Re. :Re: Re. :Re: Re. :Re: Re. :Re: Sorting column in ASCII mode

johnw — Wed, 07 Jul 2010 18:16:50 GMT

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.

Re. :Re: Re. :Re: Re. :Re: Re. :Re: Re. :Re: Sorting column in ASCII mode

Thu, 08 Jul 2010 08:43:03 GMT

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);

Re. :Re: Re. :Re: Re. :Re: Re. :Re: Re. :Re: Sorting column in ASCII mode

johnw — Thu, 08 Jul 2010 17:56:50 GMT

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*