Order of text values

[#1]

Hi.

I have a problem with ordering the values of a field in a list box.

In a products table I have a group field (gp), a code field (code), a description field (desc) and a field named "IDProduct" which result from concatenate the other three:

gp&' | '&code&' | '&desc as IDProduct

In the list box I select "Order by text" and the result is this:

But the correct order should be like this:

Could anyone help me solve this?

Thanks in advance.

[sinanozdemir]

Hi,

I tried the below expression in a list box:

And then, sort it as text in an ascending order:

Hope this helps.

[#3]

Thanks, it works.

But I have nearly 250 thousand records in the products table besides other tables in the app. So this solution will slow down the app.

[qlikviewwizard]

Hi,

Use autonumber(gp&' | '&code&' | '&desc) as IDProduct.

Later sort the column on number which is very faster. Hope this will help you.

[petter · Accepted solution]

What QlikView calls Text Order is what is often called "Natural Sort" these days. Look up the term in Wikipedia for a full explanation.

Strangely enought the Load Script does "Natural Sort" while the chart engine does an ASCII-sort. That is why Sinan's suggestion works.

If you want to force QlikView to do an ASCII-sort in the Load Script - this is a way you can do it:

The vertical bar that gets inserted between each digit will break the Natural Sort of QlikView so it will do a character by character sort as there will be no more consecutive digits anymore. MapSubstring() function will insert these vertical bars and then doing a RESIDENT load with an ORDER BY on this new temporary field we can get the correct sort order and assign it by using RowNo(). By combining the the product string with the sort order number into a dual field named Product we achieve that QlikView will use the number to denote the sorting order of this field but use the string as the displayed part of the same.

[petter]

[#7]

This is what I'm looking for, thanks!

I just added the letters of the alphabet to the mapping table since the group ID is alphanumeric.

And also it works without the Dual function for the Product field, I just set the order by load.

Thanks a lot for your help.

[petter]

Then you should close this thread an mark it as answered