Is there a way to control a text field's sort order globally without botching text alignment?

[mmarchese]

I often have a special value in a text field, and I would like that value to be shown at the bottom of lists and last in graphs.  For example, say I have a Fruit field with these values, shown in the order I would like, which is alphabetical except for NONE being last:

Apples, Grapes, Oranges, NONE

In visualizations, I can control the sort order and get this result.  But there are places where the field choices appear that cannot be controlled in this manner.  For instance, what about when I click the search button at the top of the Fruit column in a table visualization?  There is no interface that lets me customize that list's sort.  Plus, I don't want to have to set the sort order in every single visualization.  I want to set it once.

I have found that if I use Dual('NONE', 1) instead of just 'NONE', I can get that value to appear last globally, which is exactly what I want.  But this is not good enough, because the text alignment is based on whether it's a string or a number/date.  If I use this technique, Apples, Grapes, and Oranges will be left-aligned, as they should be, and NONE will be right-aligned, which is ugly.  If I do Text(Dual('NONE', 1)), then I'm back to the wrong, purely-alphabetical sort: Apples, Grapes, NONE, Orange.  I suppose I could probably work something out that makes every value into a number dual, but then everything would be right-aligned, which is not what I want for a text field.

I also tried using symbols instead of NONE, but Qlik treats symbols as earlier in the alphabet than letters, so that doesn't work either.

I would have thought this would have been a strength of the whole concept of duals, but it seems not to be the case.  If only I could do something like this, I'd be set: Dual('NONE', 'zzzzzz').  Or something like this: LeftAlign(Dual('NONE', 1))

Is there any way to keep everything left-aligned but control the sort order globally for a text field?

[sunny_talwar]

Not sure if you like this option, but what if you add an extra space before all Fruits except NONE... (Make sure to set Verbatim = 1)

SET Verbatim = 1;

Table:
LOAD * INLINE [
	Fruit
    " Apples"
    " Grapes"
    " Oranges"
    NONE
];

[mmarchese]

Hah, thanks, yeah that's actually another thing I tried.  While it does work, I don't like the spaces, especially since I know users will eventually download the data to Excel and discover the data's dirty secret 🙂

Am I out of luck as far as finding a standard solution goes?

I can't be the first to come up against this.

[sunny_talwar]

I have had to address this in different objects, but not dashboard wide. 

[sunny_talwar]

How about None with a special character in front of it

Table:
LOAD If(Fruit = 'NONE', Chr(930)&Fruit, Fruit) as Fruit;
LOAD * INLINE [
Fruit
Apples
Grapes
Oranges
NONE
];

[usamabinsadiq]

hi,

Why don't you create a numeric field when loading data using if statement in same table like 

table:
load
    yourfield,
    if(yourfield='NONE' or yourfield='zzzzz',9,1) as yourfieldsort
from table;

and when using this dimension in chart go to sorting and use sort by expression and put yourfieldsort there and select ascending.

I hope this helps.

Regards, 

[mmarchese]

Sunny_talwar:

Thanks, your idea to use chr() got me thinking.  I don't like char(930) in particular because it just shows up as a rectangle for me, but there are other choices that are ok.

I ran this to see what options I had:

Chars:
LOAD
    RowNo() AS code,
    Chr(RowNo()) AS char,
    If(Chr(RowNo()) > 'z', 'Yes', 'No') AS isAfterZ
AutoGenerate 150000;

The two ideas I came up with from that experiment are:

1) The Greek letter Nu, which looks identical to the ASCII letter N: Chr(925) & 'ONE'

You end up with something that looks like NONE but sorts the way I want it, with NONE at the end.  The downside to this is that when people export the data, if they do a text search, they will be confused as to why they get no hits when they type NONE in Excel's find dialog.  I will have to teach them to copy and paste it instead of typing it.

2) One particular Unicode hyphen that Qlik treats as coming after Z, unlike the regular ASCII hyphen: Chr(11451)

This has the same downside as #1 in that it will trick people into thinking it's a regular hyphen, but maybe it's slightly less insulting/annoying than a fake N.  I suppose I will go with this for now.  Ultimately, I still consider this a hack and wish there were a better way, but what can you do?

The relevant parts of my code now look similar to this:

Let blank = Chr(11451);
Set ReplaceBlanks = If(Len(Trim($1)), $1, '$(blank)');

Fruit:
LOAD *
INLINE [
    Fruit
    Apples
    Grapes
    Oranges
    $(blank)
];

SomeOtherTable:
LOAD
    $(ReplaceBlanks([Some Field])) AS [Some Field],
    ...
FROM ...

[mmarchese]

Usamabinsadiq:

Thanks, but sorting expressions are not what I was looking for.  As stated in the question, they don't cover every case (like the search interface at the top of each column in table visualizations) and they have to be specified once per visualization, which is tedious to set up and unnecessarily difficult to maintain.

My zzzzzz example was an attempt to show what Dual would look like if it could accept one string to display and one string to sort by, but it cannot.  It accepts one string to display and one number to sort by.  I consider that a weakness of Dual().  As it stands, Dual() converts the field to a number, which makes it right-aligned.  For some reason, Dual() was implemented in such a way that it conflates sort order and data type.

[sunny_talwar]

Amazing!! I am glad you were able to work your way through this.

Best,
Sunny