topic Re: Order of text values in QlikView

Order of text values

Mon, 07 Sep 2015 21:48:12 GMT

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.

Re: Order of text values

sinanozdemir — Mon, 07 Sep 2015 22:38:49 GMT

Hi,

I tried the below expression in a list box:

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

Hope this helps.

Re: Order of text values

Wed, 09 Sep 2015 00:44:48 GMT

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.

Re: Order of text values

qlikviewwizard — Wed, 09 Sep 2015 02:04:27 GMT

Hi,

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

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

Re: Order of text values

petter — Wed, 09 Sep 2015 15:00:57 GMT

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.

Re: Order of text values

petter — Wed, 09 Sep 2015 15:10:32 GMT

Re: Order of text values

Thu, 10 Sep 2015 17:02:54 GMT

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.

Re: Order of text values

petter — Thu, 10 Sep 2015 19:57:20 GMT

Then you should close this thread an mark it as answered