Useful Alternate Sort Method for Listboxes

It’s a Friday so how about a simple but really useful post about how to make certain listboxes that little but more user friendly.

Basically the aim here is to get around the problem you sometimes encounter when you have a long list of values that you want to sort say alphbetically but within the list there are several values that the mojority of people will look for. The most common example of this when progressing through a websites shopping cart and you’re asked to complete the country, whats at the top; Afganistan, Albania, Algeria and American Samoa no doubt – all big internet shopping nations I’m sure; but not compared to lets say the US and UK. Coupled with that countries (just as easily sales people, products etc) can have various derivations of thier name; I’ve lost count of the number of times I’ve scrolled down a country dropdown looking for ‘United Kingdom’ only to find it’s listed under ‘Great Britain’ or ‘England’; not the biggest issue in the world but one we can address in Qlikview where users may need to find values quickly and easily.

Not Better in All Circumstances; Just Some

 So in this example we’re going to pull 2 values (‘United Kingdom’ and ‘United States’) to the top of the list whilst keeping the rest ordered Alphabetically below. To do this simply go to the Sort tab of the listboxes Properties dialog and check the Alphabetical > Ascending button, set the State to Auto Ascending and also check the ‘Expression’ button and enter the following expression:

=if(Country=’United Kingdom’,0,if(Country=’United States’,0,1))

Obviously our listbox dimension is named ‘Country’. This essentailly assigns a value of 0 to our 2 nations and a 1 to the rest meaning that when the expression result is sorted in ascending order they jump to the top making it easy to find them when regularly needs. I’ve also added the dashed line into the actual dataset (simple concatenation onto Country) to create the seperation and make the list more intuative; as it doesn’t relate to the rest of the data selecting it has no impact.

As a slight alternate we could split the field into 2 as in the left hand listboxes by creating expression driven listboxes (select from the bottom of the field list) with either of the following 2 expressions:

=if(Country=’United Kingdom’ or Country=’United States’,Country)

=if(Country<>’United Kingdom’ or Country <>’United States’,Country)

Personally I’m not a fan of this second method as it makes it hard to select from both listboxes.

Obviously if you want to pull many values to the top this solution isn’t the best; to do that you should create a numeric order mapping and then use the dual() function to sort the listbox by the mapped value.

Hope you can put it to use.

All the best,

Matt

Advertisements
Comments
4 Responses to “Useful Alternate Sort Method for Listboxes”
  1. Hi Matt,

    Quick alternative to using an If statement (and better if you have a few more values) is to use Match, e.g.:

    =-Match(Country, ‘Ireland’, ‘UK’, ‘USA’)

    Match will return the number of the value in the list.

    The – is needed because the default of 0 will put the other countries above our list.

    My listbox will therefore be listed: USA, UK, Ireland, Argentina, …

    I use this a lot when I have to modify a document that someone else has loaded and I can’t do a reload to set a Dual. For example, the old “Apr”, “Aug”, “Dec”,… problem. I can set a sort order on the listbox of:

    =Match(Month, ‘Jan’, ‘Feb’, ‘Mar’, ‘Apr’, ‘May’, ‘Jun’, ‘Jul’, ‘Aug’, ‘Sep’, ‘Oct’, ‘Nov’, ‘Dec’)

    Regards,

    Stephen

  2. Aaron Couron says:

    I really like this tip. It is something I expect as a consumer but never thought about doing in QlikView. I will definitely use this.

  3. aadilm says:

    Thanks for this tip…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: