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.

About The Author

Barry Harmsen

Hi there, I'm Barry and I'm a Business Intelligence Consultant at Bitmetric and based in the Netherlands. Originally from a background of 'traditional' Data Warehousing, Business Intelligence and Performance Management, for the past 10 years I have been specializing in Qlik and a more user-centric form of BI. I have done numerous QlikView and Qlik Sense implementations in many different roles and industries. In 2012 I co-authored the book QlikView 11 for Developers. You can follow me on Twitter at @meneerharmsen.

5 Comments

  • 1
    Abhijit
    April 15, 2011 - 06:29 | Permalink

    below is my query,
    i’m loading data form database, it is not possible to load data using inline wizard..
    please help me how to achieve custom sort order at script level.
    i’ve tried sort by expression at report level to achive custom sort but it does not work on calculated dimensions. how to achieve this?

    • 2
      April 16, 2011 - 17:00 | Permalink

      I don’t see a query in your comment, but if you are loading from a database can you not apply the sort in your SQL statement? That way you can simply use the “load order” sort option.

  • 3
    David Dery
    April 18, 2011 - 22:00 | Permalink

    Not if loading from a SQL view (my current problem). I appended the QV Load SQL Statement to add the order clause, but QV appears to ignore it.

    LOAD
    Concat(Distinct CALENDARDATE, ‘,’) As DateList;
    SQL select CALENDARDATE from $(QVServer).dbo.vwQVMasterCalendar order by CALENDARDATE;

    In my case I am attempting to use the list I’m creating as a constraint for a drop down list for an input box, the input box object has no sort properties. For example, my list currently comes up as 01/01/2008, 01/01/2009, 01/02/2008, 01/02/2009 and so on, where of course I’d prefer to have it in actual date order.

    Any thoughts?

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    Read previous post:
    QlikView blog round-up: Selling QlikView (and shares)

    This weeks QlikView blog round-up is all about selling, selling software and shares that is. How I sell QlikView QlikTech...

    Close