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.

[Anonymous]

You're right.  I just conveniently had the correct load order.  Looks like the substitute is the only reliable solution.

[Anonymous]

See if this helps

Number 10 in sort expression can be replaced with anything equal or greater than the max length of the field.  You can calculate in a variable if you wish, or just use a number large enough.

[#4]

Thanks everyone. I'm still reading through the responses, but I can add that my example was a basic one. The data is massive and as varied as hundreds of thousands of addresses worldwide can be.

Sort order won't work because the column value is created using a function. All non-alphanumeric characters and spaces, for example, are stripped.

[swuehl]

Michael,

try adding a value like  POBOX101 first in the data load. It won't sort in correctly.

In general, I believe the sort expression should return a sort order number, not a text.

Brian,

I haven't understood what you mean with your last two sentences, could you give an example?

[#6]

I don't think it's too important in the context of the issue, but if you're curious... I'm pulling the address data from the source database in components, and then in QlikView, I'm concatenting those components (addr1 + addr2 + city, etc.), running them through a custom function, and putting the resultant value into a new column. "P. O. Box 100" becomes POBOX100. The idea is to group like addresses as much as possible. (See thread 411921.) As such, I can't really use load order for sorting here.

[swuehl]

Why not? Have a look at the attached sample from the second referenced thread above, it doesn't really matter if you load the values in or calculate the values in the script.

Of course, additional steps are necessary to get your values sorted by load order, this might include additional temporary tables etc.

[#8]

In that sense, then, I guess there are ways. Thanks for pointing it out. It's more effort than I'm willing to go through at this point, as this is a very small part of the overall solution.

Curious, has QlikTech spoken on this issue? Is it considered by design or a bug, and, if the latter, do they have a fix date or version in mind?

[Anonymous]

Hmm...  Not my day 😞

OK, last attempt, rather an idea than a practical solution.  Trying to do manually what text sort is supposed to do automatically.

[Clever_Anjos]

A little trick is concatenate a blank after the string using a expression into ListBox

Field & ' '  will sort right but introduces another bug (an empty line)

This works

=if(len(Field & ' ')>1,Field & ' ')

[#11]

Thanks. That's good, but it has other problems. For example, if I make a selection from the box, none of the data in the other list boxes becomes selected, presumably because we no longer have a match between the value in the list box and the value in the table. So I tried building the string to include the trailing space natively. That works better, but it still sorts numbers at the beginning of the string numerically.

8MAINST

09MAINST

9MAINST