24 Replies Latest reply: Aug 10, 2017 2:15 PM by Danny Selgo

# 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.

• ###### Re: Sorting Fields with special characters

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

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

• ###### Re: Sorting Fields with special characters

I agree, this looks more like an issue..

• ###### Re: Sorting Fields with special characters

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

• ###### Re: Sorting Fields with special characters

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

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

• ###### Re: Re: Sorting Fields with special characters

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

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

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

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: Re: Re: Sorting Fields with special characters

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

• ###### Re: Sorting Fields with special characters

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;

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

Yes, it works with SortOrder sort order.

• ###### Re: Sorting Fields with special characters

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

• ###### Re: Sorting Fields with special characters

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.

• ###### Re: Sorting Fields with special characters

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 need to correct myself, sort by Numeric Value doesn't work in 11.20, but the SortOrder sort order seems to be correct even without using the latest modifications.

Clever Anjos, I think your sample misses the case

1.5"Something

Which should be preceded by 1.25-7

• ###### Re: Re: Sorting Fields with special characters

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,

DV

• ###### Re: Re: Sorting Fields with special characters

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

• ###### Re: Sorting Fields with special characters

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

• ###### Re: Sorting Fields with special characters

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

• ###### Re: Sorting Fields with special characters

It seems to be a bug of some versions, have tried into 11.00SR1 and sorts ok

• ###### Re: Sorting Fields with special characters

I´ve found a small trick to fix this issue

Instead of using the field into listbox, I´ve used a field & ' ' concatenation.

It seems to fix it

PFA the qvw

• ###### Re: Sorting Fields with special characters

I know this thread is three years old, but in case someone find it by using the search (it's ranked quite high)

I think this works as expected:

• ###### Re: Sorting Fields with special characters

Thanks for going back to post this solution Stefan!