Alphabetical sort by an expression in Qlik Sense

[#1]

Let's say there is a list of capital cities in a table (single column for simplicity) and I'd like to sort them by country which is not a column in the table. I'd like to achieve this with sort by an expression, however this does not work as expected and it does not use alphabetical order but rather the order which the records from countries dimension were loaded in.

I tried the match function which works if you provide the countries in a correct order, but that is really not a good solution, since the country dimension wouldn't be a few records.

To generalize: I'd like to be able to use the sort by an expression, where the expression results in a string and I'd like to keep alphabetical order by that very expression. I need a solution where I can count on the sorting even if the dimension and relations between them changes quite frequently. Also loading the dimension pre-ordered is not a generic solution - might not suit for some scenarios.

Tried with Qlik Sense Desktop and Enterprise 3.0

Thank you for your input.

[jaumecf23]

I also have checked what you say and it doesn't work.

I propose a solution that it works but i don't know if this will be helpful for you. Creating an extra numeric field in the script that represents the order alphabetically. For example if you have three Countries like USA, France and Germany. You will have that:

Country - Numeric Field

France -> 1

Germany -> 2

USA -> 3

Obtain this numeric value can easily be done in the script.

Then if instead of using the Country in the sorting seettings, you use the Numeric value field it's working correctly for me.

[jaumecf23]

Propose to obtain the numeric field value:

Test:

Load Country,

RowNo() as NumCountry

Resident CountryTable

order by Country asc ;

[#4]

Thank you Jaume, this is a helpful hint, however does not work in a generic way. Lets imagine a table (not calling it dimension) where there are countries and next column is continent. I assume I'd need to load the table twice and generate RowNo() for countries and continents separately if I want to use sort by country in one visualization and by continent in another. Later on in the script, it might make sense to join those back.

The disappointment in this is that it's a very basic scenario and you need to provide a workaround to force it to work. First of all this is a non-self-service approach, since a business guy won't achieve this in most cases and would moan about the tool.

Thank you though, this is a useful workaround if you are the one who does the modeling in Qlik Sense!

[nhenckel]

I achieved this by using a combination of rank() and minstring(). See below.

ABC order
=rank(MinString(dim_name))

ZYX order
=rank(MinString(dim_name)) * -1