topic Re: Sorting Fields with special characters in QlikView

Sorting Fields with special characters

jpapador — Wed, 09 Oct 2013 20:18:34 GMT

Anyone know how Qlikview considers characters like " and - when Sorting a field A --->Z.

In this example I am having trouble figuring out why 1.5"STD is above 1.25-7 HEX.

Re: Sorting Fields with special characters

rbecher — Wed, 09 Oct 2013 20:21:03 GMT

However the sorting works on those characters. It's a better way to have a extra field for sorting where you can strip or replace those chars.

- Ralf

Re: Sorting Fields with special characters

jpapador — Wed, 09 Oct 2013 20:28:14 GMT

Well without characters like the . 125 would be lower that 15 still.

Re: Sorting Fields with special characters

rbecher — Wed, 09 Oct 2013 20:38:38 GMT

I agree, this looks more like an issue..

Re: Sorting Fields with special characters

rbecher — Wed, 09 Oct 2013 20:42:41 GMT

Maybe have a mapping table with a sorting number per piece derived from the size or so..

Re: Sorting Fields with special characters

swuehl — Wed, 09 Oct 2013 21:29:21 GMT

This is quite a complex issue. Shouldn't something like 1/4" be even sorted on top?

In this example I am having trouble figuring out why 1.5"STD is above 1.25-7 HEX.

I assume (but I don't know exactely), it's similar to your other recent issue. QV sorts the values also by block of numbers (like a somewhat natural sort), so 5 is preceding 25 is preceding 50 is preceding 875:

Number Block '1' / Char Block '.' / Number Block '5'

1.5"STD

1.25-7

1.50"

1.875"x6"

I don't know the answer to your issue. But maybe try to separate the first part that seems to build a number and evaluate this part to a number. Then use this number for sorting.

Re: Sorting Fields with special characters

Clever_Anjos — Wed, 09 Oct 2013 21:40:03 GMT

I have tested into 11.20SR3, it really seems to be a bug

Re: Sorting Fields with special characters

rbecher — Wed, 09 Oct 2013 21:44:40 GMT

Interesting solution but still a bit strange that a Listbox on SortOrder has the same wrong sorting..

Re: Re: Sorting Fields with special characters

IAMDV — Wed, 09 Oct 2013 21:48:59 GMT

Hi,

I think there is method to madness. According to my research, QlikView sorts strings (A-Z) based on the ASCII values of each character. It may seem complicated but it's easy once you understand the logic. While sorting decimals (referring to 1.25 and 1.5) QV treats them as ASCII characters but not decimal numbers.

In your case, special character have following ASCII values.

" (Double Quote) has 34

- (Hyphen) has 45

. (Period) has 46

5 (Number Five) has 53

2 (Number Two) has 50

For example, if you have string 1.5!STD will be the shown as first value compared to 1.5"STD.

You need to use ORD() QlikView function which converts ASCII characters to Integers which can be used for sorting.

I hope this helps and makes sense!

Good luck!

Cheers,

Re: Re: Sorting Fields with special characters

swuehl — Wed, 09 Oct 2013 21:49:08 GMT

Ralf,

I don't know for sure what is right or wrong.

My Listbox shows the values sorted like

1/4"X24"X50'

2/3"

1NMR

1.25-7

1.5"STD

1.50"

1.875"x6"

which seems correct to me with my limited understanding.

Re: Re: Sorting Fields with special characters

rbecher — Wed, 09 Oct 2013 21:53:39 GMT

But this is not happen. The strings get ordered by value of sub blocks as Stefan mentioned..

Re: Re: Sorting Fields with special characters

rbecher — Wed, 09 Oct 2013 21:56:03 GMT

I meant a Listbox on the field SortOrder doesn't work well. It only work rigth if sorting is set to Numeric Value which isn't understandable because it should use the numeric value of the dual first. Also, num(SortOrder) or num#(SortOrder) doesn't work. Maybe it's a mix of data types in the field SortOrder.

Re: Re: Re: Sorting Fields with special characters

swuehl — Wed, 09 Oct 2013 22:08:25 GMT

I think it's getting too late for me.

Sorry, Ralf, I don't see what you are talking about (maybe because of different versions, I noticed I am currently on an older QV 11 version), but I haven't used the dual() function also.

Interestingly, I just sorted the Text field (where all began...) by Numeric Value, and it returned the same (correct?) sort order as after all the work...

I am attaching this version also, and try to open the file tomorrow with a latest version.

Re: Re: Re: Sorting Fields with special characters

IAMDV — Wed, 09 Oct 2013 22:11:23 GMT

Hi Ralf,

I've loaded Stefan's sample data and tried to sort by Text (A-Z). I get the expected results as per ASCII values. Attaching the same for your reference.

Cheers,

www.QlikShare.com

Re: Re: Re: Sorting Fields with special characters

rbecher — Wed, 09 Oct 2013 22:13:12 GMT

I'm on v11.2 SR3 and the Text Listbox with Sort by Numeric Value shows the wrong order. So, this is also version dependent?

Btw. I made no change to the Listboxes. It's from comm95340_2.qvw

Re: Sorting Fields with special characters

swuehl — Wed, 09 Oct 2013 22:22:55 GMT

DV,

in your screenshot, 1.5" precedes 1.25, I think that is not what is required by the OP.

Ralf,

yes, version seems to be an issue. In your and my setting, I notice that some SortOrder values have different decimal separator, more precisely the ones which were Evaluate()'ed (which might explain some issues).

For now, I changed the script to

SET DecimalSep='.';

SET ThousandSep=',';

LOAD * , num(Evaluate(SubText)) as SortOrder;

LOAD *,

Left(Text,FindOneOf(lower(Text),'abcdefghijklmnopqrstuvwxyz-"')-1) as SubText

INLINE [

Text

1.5"STD

1.25-7

1.50"

1.875"x6"

1/4"X24"X50'

1NMR

2/3"

];

which did correct the decimal separator. Does this affect your SortOrder sort order?

Re: Sorting Fields with special characters

rbecher — Wed, 09 Oct 2013 22:27:23 GMT

Yes, it works with SortOrder sort order.

Re: Sorting Fields with special characters

IAMDV — Wed, 09 Oct 2013 22:49:13 GMT

I agree Stefan. I was not providing the answer but I'm just saying that QV sorts based on ASCII value. That was the initial question...

Thanks,

Re: Sorting Fields with special characters

Wed, 09 Oct 2013 23:00:48 GMT

You can use the Load Order to sort, if data is correctly sorted in data source

Re: Sorting Fields with special characters

swuehl — Thu, 10 Oct 2013 10:22:41 GMT

DV,

it seems that QV is not sorting based on ASCII values (or maybe I am misunderstanding your point).

Let's look at these text values

A10

Wouldn't a pure ASCII sort lead to this order?

A10

While QV returns first sort order when sorting by Text.

I don't think this is necessarily bad (except that AFAIR, it's bot consistent with the docs). It may be more consistent with what a user expects in most cases.

I believe, QV developers won't spend much time in inventing something new, instead they will call a system function. Let's see. Maybe this one:

StrCmpLogicalW function (Windows)

Seems close to what we see.

Ralf,

I've checked with another version of 11.20 SR3 (32 bit) and did get some different results from my findings yesterday evening and even from yours (numeric sort works at my site also for the Text field...).

I am afraid this seems to get a versioning issue, too.