Creating a custom sort order

By Barry Harmsen

QlikView offers quite a few ways to easily sort dimensions in your listboxes, tables and charts: by frequency, numeric, text or load order. But what if you want to use a custom sort order that does not follow one of these patterns?

Consider, for example, the following scenario; we have three Business Lines: A, B and C. These Business Lines always need to appear in the order B, A, C. This type of ordering cannot* be achieved by using any of the default sort orders, we will have to create a custom sort order. This post will describe two methods for doing this.

You can download the example qvw by clicking here.

Method 1: Assigning a sort order value in the load script
This method works by assigning the sort order value in the load script.

BusinessLines:
LOAD * INLINE
[
Business Line, Sort order
Business Line A, 2
Business Line B, 1
Business Line C, 3
];

The example script above shows an additional field ([Sort order]) that defines the sort order. This field can now be used in the sort expression field of your listbox, table or chart, as shown in the image below.

Custom sort using an extra field in the load script.

If you want to make this sort order the default sort order for a field, you can also add it to the sort tab of your document properties (via Settings -> Document properties or via Ctrl + Alt + D).

Method 2: Using the match function
The match function takes an input value and a range of values to compare it against and returns the number of the value that it matched, or 0 if no value was matched.

Using this function we can define the following expression:

=match([Business Line], ‘Business Line B’, ‘Business Line A’, ‘Business Line C’)

This expression will return 1 for Business Line B, 2 for Business Line A and 3 for Business Line C.  This expression can now be used in the sort expression field, as shown in the image below.

Sort options in QlikView

Keep in mind that the match function does a case-sensitive comparison, so “business line a” would not be matched. For a case-insensitive comparison use the mixmatch function. To include wildcard characters use the wildmatch function.

Which method to use?
So, which method should be used when? Generally, the method of assigning a sort order value in the load script is the preferred method, especially if you have to sort many values. However, in cases where you cannot (for example, in the case of a calculated dimension) or do not want to add a field to the data model the match function method is the best choice.

You can download the example qvw by clicking here. If you have any questions, feel free to ask them in the comments section.

* If you load the data in the desired order from the source, you could also use a load order sort.

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.

13 Comments

  • 1
    Bruce
    October 25, 2010 - 23:27 | Permalink

    There is a 3rd way that I know of. I used to use your method #1, but know I do a “pre-load”. Then I just sort by load order.

    Somehting like:

    PreLoad:
    LOAD * INLINE
    [
    Business Line
    Business Line B
    Business Line A
    Business Line C
    ];
    Sales:
    Load Customer, Business Line, Sales, Date
    From data;
    Drop table PreLoad;
    QV always has the default load order, so we loaded as we wanted it, then we bring in raw data.

    Cheers

    • 2
      October 26, 2010 - 07:17 | Permalink

      Bruce,

      Good point! I had the load order part of that option in the small print at the bottom of this post, but first singling out the field to be sorted in a separate load makes it a lot more workable. I will definitely try out this method.

      Cheers,
      Barry

  • 3
    Manish
    May 6, 2011 - 10:19 | Permalink

    Hi,

    Can you please guide me how to use firstsortedvalue() function?

    Thanks

  • 4
    Anil
    January 23, 2012 - 12:56 | Permalink

    Hi Barry, Bruce

    Here i have one more method for custom sorting.this i have implemented also

    If([Column name] =’Business Line’, 1, (If( [Column name] =’Business Line’, 2,(If( [column name] =’Business Line’, 3)))))

    Just review it and please give me your comments.

    • 5
      January 24, 2012 - 15:16 | Permalink

      Hi Anil,

      That works as well, but it is really just a more complicated version of the “match” solution. Since this post, I have moved to the “load order” solution that Bruce posted. I only use the “match” or “sort order in script” approaches when absolutely necessary (e.g. I am protoyping with a client and need something fast).

      Kind regards,
      Barry

  • 6
    robnever
    September 12, 2013 - 14:43 | Permalink

    Check out the Dual() function. Have the text value be A, B, C, and the num values be 2, 1, 3 (or whatever)

  • 7
    Maw
    October 16, 2013 - 15:49 | Permalink

    Hy Barry,

    I tried you first method and it didn’t work …
    I don’t understand why.

    I created two new tables in the script with
    – in the first colum the name of the variables and in the second the alphabet for the first table,
    – and for the second table the same thing but with number.

    I did that because i have two dimensions to sort and for some of them they have the same name.

    I did a PivotTable on Qlikview and I want to sort the dimension in a specific order.
    I selected the option “Expression” and I did the same thing you did.

    But I saw you put the new variable “Sort Order” in your “Priority”.
    But if I do that, the variable will be visible in the table, isn’t it?

    Thanks for your answer.

    Maw

  • 8
    Dhananjay
    October 23, 2013 - 17:08 | Permalink

    Hi Barry,

    Can i use order by in resident table??
    if yes How???

  • 9
    April 2, 2014 - 14:26 | Permalink

    Thank you very much. It worked perfectly.

    Greetings from Spain!

  • 10
    sarfaraz Sheikh
    March 17, 2015 - 14:44 | Permalink

    Dear Barry,

    I am stuck in one problem…I want to achive like below however the i am not getting the Fabulous as dimension content…I saw your post so i thought let me ask you ..

    if(D_NO=1,’Excellent’,
    if(D_NO=2,’Very_Good’,
    if(D_NO=3,’Good’,
    if(D_NO=4,’Average’,
    if(D_NO=5,’Need_Improvement’,
    if(Wildmatch(D_NO,1,2),’Fabulous’
    ))))))

    Is it possible in QlikView to achive like above on chart level if yes then how i can achive the same…

    awaiting the quick reply from your side ……

    Sarfaraz

  • 11
    ketty
    May 26, 2017 - 11:06 | Permalink

    hi experts,
    if have two charts with different dimensions, is possible to sort one chart base on another chart.
    example chart-1 have dim-1, chart-2 have dim-1 and dim2, we want chart 2 sorting same as chart 1.

    thanks

  • Leave a Reply

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

    Read previous post:
    Moving an object, one pixel at a time

    The alignment toolbar offers some excellent options to lay out the QlikView objects on your sheet. However, sometimes you may...

    Close