Sorting Fields with special characters

[jpapador]

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.

[rbecher]

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

[rbecher]

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.

[swuehl]

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.

[IAMDV]

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,

DV

www.QlikShare.com

[rbecher]

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

[swuehl]

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?

[rbecher]

Yes, it works with SortOrder sort order.

[IAMDV]

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,

DV

[#10]

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

[swuehl]

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

A1

A2

A10

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

A1

A10

A2

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.