Creating a custom sort order: load order & dual

By Barry Harmsen

Dual or Duel? Couldn't find a fitting picture to visualize the concept of sorting, so I decided to make a lame joke instead ;) Excellent movie though, really had me on the edge of my seat the entire time.In a previous post I described how to create a custom sort order in QlikView by assigning a sort order value in the load script or by using the match function. This post describes two other clever methods that I recently became aware of:

Read on to see how these methods work.

(Everything described below is also demonstrated in an example qvw, which you can download by clicking here.)

Using the load order

This method was suggested by Bruce in response to my original post about custom sort orders.

When a field is loaded for the first time, QlikView remembers the load order. We can use this by first loading the field sorted in the correct order before we load any other data. In this example we want the business lines to be in the order B, A, C.

Preload the Business Lines sort order

Next, we load the additional data. Don’t worry about the sort order.

Load the additional data

Once the data has been loaded we drop the table containing the preloaded sort order, we do not need it anymore.

Drop the presorted table

Now, when we can sort our data in the predetermined sort order by going to the sort tab, selecting the Business Line column and choosing to sort by Load Order.

Original Load Order

Using the dual function

The Dual function lets you assign both a string and a number to a single field. In this example we create a field called Dual Business Line which contains two values; the name of the Business Line as text and the sort order as an integer.

Dual Business Line

Once the data is loaded the Dual Business Line field can be interpreted as both a string and a number. We can use this knowledge to set the correct sort order by going to the sort tab, selecting the Business Line and choosing Numeric Value as the sort order.

Numeric sort order

The advantage of this method over just creating a second field with the sort order (as was shown in the previous post) is that both values are stored in the same field. You will not have sort order fields, which might confuse end users, showing up in the front-end. The downside is that by default QlikView treats fields with dual values as numbers, which can be annoying when it comes to formatting.