Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jpapador
Partner - Specialist
Partner - Specialist

Sorting Fields with special characters

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.

24 Replies
rbecher
MVP
MVP

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

Astrato.io Head of R&D
rbecher
MVP
MVP

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.

Astrato.io Head of R&D
swuehl
MVP
MVP

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
Luminary Alumni
Luminary Alumni

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
MVP
MVP

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?

10.10.png

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

Astrato.io Head of R&D
swuehl
MVP
MVP

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
MVP
MVP

Yes, it works with SortOrder sort order.

Astrato.io Head of R&D
IAMDV
Luminary Alumni
Luminary Alumni

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

Not applicable

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

swuehl
MVP
MVP

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.