topic Re: Listbox - text sort not ASCII? in QlikView

Listbox - text sort not ASCII?

Fri, 01 Nov 2013 12:52:18 GMT

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.

Re: Listbox - text sort not ASCII?

swuehl — Fri, 01 Nov 2013 12:58:00 GMT

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

Re: Listbox - text sort not ASCII?

Clever_Anjos — Fri, 01 Nov 2013 12:59:22 GMT

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

Re: Listbox - text sort not ASCII?

Fri, 01 Nov 2013 13:08:33 GMT

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

Re: Listbox - text sort not ASCII?

Anonymous — Fri, 01 Nov 2013 13:12:00 GMT

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.

Re: Listbox - text sort not ASCII?

Gysbert_Wassenaar — Fri, 01 Nov 2013 13:24:08 GMT

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

Re: Listbox - text sort not ASCII?

Fri, 01 Nov 2013 13:26:16 GMT

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.

Re: Listbox - text sort not ASCII?

swuehl — Fri, 01 Nov 2013 13:35:35 GMT

Yes, the substituting approach might work.

Just found one of the other threads:

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

Re: Listbox - text sort not ASCII?

Anonymous — Fri, 01 Nov 2013 13:40:14 GMT

Oh I got it the opposite way...

This works for me, just tested, sort by expression

=text(Field)

Re: Listbox - text sort not ASCII?

Gysbert_Wassenaar — Fri, 01 Nov 2013 13:46:54 GMT

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

Re: Listbox - text sort not ASCII?

Anonymous — Fri, 01 Nov 2013 13:52:39 GMT

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

Re: Listbox - text sort not ASCII?

Anonymous — Fri, 01 Nov 2013 14:14:11 GMT

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.

Re: Listbox - text sort not ASCII?

Fri, 01 Nov 2013 14:32:24 GMT

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.

Re: Listbox - text sort not ASCII?

swuehl — Fri, 01 Nov 2013 14:47:08 GMT

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?

Re: Listbox - text sort not ASCII?

Fri, 01 Nov 2013 15:03:00 GMT

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.

Re: Listbox - text sort not ASCII?

swuehl — Fri, 01 Nov 2013 15:10:50 GMT

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.

Re: Listbox - text sort not ASCII?

Fri, 01 Nov 2013 15:28:32 GMT

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?

Re: Re: Listbox - text sort not ASCII?

Anonymous — Fri, 01 Nov 2013 16:02:07 GMT

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.

Re: Listbox - text sort not ASCII?

Clever_Anjos — Fri, 01 Nov 2013 16:10:21 GMT

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 & ' ')

Re: Listbox - text sort not ASCII?

Fri, 01 Nov 2013 17:50:31 GMT

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