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]

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

[jpapador]

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

[rbecher]

I agree, this looks more like an issue..

[rbecher]

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

[swuehl]

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.

[Clever_Anjos]

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

[rbecher]

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

[IAMDV]

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

[swuehl]

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.