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.
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.
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.
13 Comments
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
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
Hi,
Can you please guide me how to use firstsortedvalue() function?
Thanks
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.
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
Check out the Dual() function. Have the text value be A, B, C, and the num values be 2, 1, 3 (or whatever)
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
Hi Barry,
Can i use order by in resident table??
if yes How???
Thank you very much. It worked perfectly.
Greetings from Spain!
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
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