Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to create a custom sorting in Dimension?

Hi all,

I have a dimension called Region, for example, it contains values like London, New York, Hong Kong, Japan, Singapore, Australia ..... etc

I would like to show a table with the first Dimension as Region with a custom sorting. It should show first as London, second as Hong Kong, third as New York, for the remaining order it should sort in ascending alpahbetical order from A to Z.

The possible values of Region is not constant, it could be expanding with more countries adding into the Region list.

Any suggestion how to archive it?

Thank you !

4 Replies
fernandotoledo
Partner - Specialist
Partner - Specialist

is your custom sortin static (always the same)?

If so, you can load an auxiliary table with the order you want:

Country, CountryOrder
Brazil, 1
Argentina, 2
USA, 3


then on the "sort" panel you select to sort by expression and use the expression =Only(CountryOrder)

marcel_olmo
Partner Ambassador
Partner Ambassador

So, you mean that you want these cities on the top: London, second as Hong Kong, third as New York. And the other ones in alphabetical order?

Maybe if you create a flag like 'orderfield' and you put for example London -> OrderField 1. Hong Kong -->OrderField 2, NewYork --> OrderField :3. And the other ones put orderField : (big number like 99), and sort first for orderField, and the second parameter the alphabetical order.

Hope it helps!

Not applicable
Author

Look at the "Dual" functionality and use it to load a small table similar to the method that Fernado suggested. The dual functionality is for data items that have a dual nature - both a text value and a numeric value. The text value will show up in the chart dimension but the numeric value can be used for sorting.

johnw
Champion III
Champion III

You could take advantage of the load order sort and do something like this at the top of the script:

RegionSort:
LOAD * INLINE [
Region
London
Hong Kong
New York
];
CONCATENATE (RegionSort)
LOAD DISTINCT Region
FROM wherever
ORDER BY Region
;

Then drop the table at the end of the script. I tend to mostly use load order when I need custom sort orders these days.