Listbox - text sort not ASCII?

[#1]

Here's a hopefully quick question about listbox sorts, although I suspect it applies everywhere. My listbox is configured to sort by state (auto-ascending) and text (A-Z). My content is reformatted address data, and the numeric portion of my data is being sorted numerically. So I'm getting, for example,

POBOX1

POBOX2

POBOX100

POBOX200

instead of

POBOX1

POBOX100

POBOX2

POBOX200.

Am I missing something fundamental? I thought the nature of a text sort would be pure ASCII, but apparently it's not. The latter sort is what I'm trying to achieve. Thank you.

[swuehl]

Right, it seems it's not an ASCII sort, but a kind of natural sort.

There were some recent discussions about that here in the forum lately, e.g.

http://community.qlik.com/thread/95340

[Clever_Anjos]

It seems the same issue of this:http://community.qlik.com/thread/95340

[#4]

Thanks for directing me there. I missed that thread while searching on the issue.

[Anonymous]

Brian,

Try to sort by expression:

=keepchar(YourFieldName,'0123456789')

It eliminates all non-numeric characters, keeping only numbers.

Regards,

Michael

Edit: if they all start with POBOX, the expression could be

=mid(YourFieldName, 6)

It eliminates the first 5 characters.

[Gysbert_Wassenaar]

The numeric digits are considered as numbers, not strings.

So POBOX1 is POBOX + 1, POBOX100 is POBOX + 100 and POBOX2 is POBOX +2. Since 2 is larger than 1 POBOX2 follows POBOX1. And 100 is larger than 2 so POBOX100 follows POBOX2. The order of the three thus becomes:

POBOX1

POBOX2

POBOX100

No idea how to get the sort order you want though, other than fixating it in the script. Perhaps by sustituting a numeric digit with another character with a higher ascii value so it's not considered as a number anymore

[#7]

Sorting by load order might be an option

Then you need to prepare by adding an inline table to your script with the preferred sort order. This is done before the load of the table where the field actually belong.

SortOrder:

LOAD * INLINE [

    Field

    POBOX1

    POBOX100

    POBOX2

    POBOX200

];

When the real data table has been loaded you drop the SortOrder table.

This is way of controlling the order QV populates a field.

[swuehl]

Yes, the substituting approach might work.

Just found one of the other threads:

http://community.qlik.com/thread/95189

[Anonymous]

Oh I got it the opposite way...

This works for me, just tested, sort by expression

=text(Field)

[Gysbert_Wassenaar]

Doesn't work for me. Text(Field) sorts the same as Load Order here.