Whenever we create any chart object in QlikView, we come across the sorting. There is a sort tab present in chart properties which gives various options to sort the dimension but still some time we need to do some extra tricks to meet the requirement. There are many ways by which we can do sorting in QlikView. In this post I am trying to figure out various ways to do sorting with the help of sort tab from properties.
1.Using Load Order.
If you check the sort tab in chart properties, there we found sort by option as Load Order. This means that qlikview will sort the dimension according to the original load order while loading the table.
Now how this is going to help us in sorting?
Ans: 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.
Step 1: Load an temporary inline table sorted as the desired output
Temp:
LOAD * INLINE
[
Country
India
Israel
UK
China
USA
];
Step 2: Load the actual table
Noconcatenate
Main_Table:
LOAD * Inline
[
Country
USA
India
China
UK
Israel
];
Step 3: Drop Table Temp;
2. Using Match/MixMatch/WildMatch function:
match( str, expr1 [ , expr2,...exprN ] )
The match function performs a case sensitive comparison. It returns numerical value after match str with expr.
Example: match( Country, 'India','USA','UK')
returns 2 if Country = 'USA'
This property of Match function can be used to sort the dimension using Expression sort from chart Properties.
=Match(Country,'India','USA','UK','China','Israel')
MixMatch and WildMatch functions hold the same properties but have some additional features.
3. Using Dual function:
Dual() function has the capability to assign both text and number to a field. Using Dual function we can assign the desired numerical values to the field data and then using numeric value sort option we can sort the field.
For Example:
Country:
LOAD dual(Country,sortvalue) as Country Inline
[
Country,sortvalue
USA,2
India,3
China,1
UK,5
Israel,4
];
Once the data is loaded the Country 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.
There may be formatting issues comes after adding dual values to a field. We can change the formatting from Presentation tab.
Please feel free to give your comments if i missed something.
~ Sushil
