topic Re: Customer Sorting in QV List Box in QlikView

Customer Sorting in QV List Box

trpatel123 — Tue, 01 Mar 2016 13:38:30 GMT

I have below List of Countries..

Data:

Load * Inline

[

Country

(Not Specified)

USA

India

Germany

France

Canada

];

I want to create a List Box which will sort all Countries according to text format but (Not Specified) should come at last.

How to achieve this?

Re: Customer Sorting in QV List Box

oknotsen — Tue, 01 Mar 2016 13:52:10 GMT

Put all values in the correct order and in the list box sort by "load order".

Re: Customer Sorting in QV List Box

trpatel123 — Tue, 01 Mar 2016 14:00:59 GMT

I can't do that... It is coming from SQL database and I have more than 200 countries.

Re: Customer Sorting in QV List Box

Tue, 01 Mar 2016 14:12:19 GMT

List Box Properties: Sort ‒ QlikView

Re: Customer Sorting in QV List Box

trpatel123 — Tue, 01 Mar 2016 14:19:08 GMT

There is nothing there which can help me in my problem.

Re: Customer Sorting in QV List Box

rwunderlich — Tue, 01 Mar 2016 17:15:05 GMT

On the Sort pane, check both "Text" and "Expression". Use this for the sort expression:

match(Country,'(Not Specified)')

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Customer Sorting in QV List Box

oknotsen — Tue, 01 Mar 2016 19:07:56 GMT

What you could do is still create the INLINE table with all the values in the order you like, next load the actual data from the database, next drop that INLINE table, and still end up sorting by load order .

Re: Customer Sorting in QV List Box

trpatel123 — Wed, 02 Mar 2016 08:43:52 GMT

How can I create an Inline table if I don't know exactly how many and which countries are going to appear from database. Your solution is not the one which could help me. But yes, thanks for your reply !

Re: Customer Sorting in QV List Box

trpatel123 — Wed, 02 Mar 2016 08:46:30 GMT

Hi Rob Wunderlich,

This is what exactly I want. It's working perfectly.

Could you please let me know how it works?

Re: Customer Sorting in QV List Box

oknotsen — Wed, 02 Mar 2016 15:43:08 GMT

Here are 3 ways to create an inline table that does what you need. The actual countries in your dataset is not relevant for every solution.

The number of countries in this world is limited to about 250. A few minutes of Google can probably get you a complete list. Wikipedia comes to mind.
Alternatively you can load your data once and next make use of the tool you get with the INLINE wizard to simply create an INLINE table that lists all possible values in your data source. It is a matter of selecting the field and importing the data.
As a 3rd alternative (when you can't figure out how the previous wizard works) is to simply load your data, create a ListBox for country, export it (already sorted) to XLS, make adjustments if needed to the order, and turn that into an INLINE table.

Re: Customer Sorting in QV List Box

rwunderlich — Thu, 03 Mar 2016 06:44:08 GMT

Match returns the number of the element that matches, zero if no match.So for:

match(Country,'(Not Specified)')

If Country = '(Not Specified)', the match returns 1. For all other Country values it will return 0. So the 0 values will be sorted first, followed by the 1 value. The multiple 0 values will then be sorted by the secondary sort of Text.

Please mark an answer as correct if you have a solution.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com