Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
When sorting by text QlikView uses phonebook order (p1, p2, p10, p11) and not ASCII mode (p1, p10, p11, p2) as in many other systems. It treats sequence of digits as one number and orders strings depending on the value of the number. So e.g. "9" goes before "10" and not vice versa.
To have our strings ordered in ASCII mode at first we need to create an ordering field by copying the source field's content into new text field. Digits should be separated by non digit symbol. In the code below I separated all symbols, not only digits, by "-". For example string "p100" becomes "p-1-0-0" in the ordering field.
In the next step we copy original field as is into a new and use the "separated" field for sorting. Because digits a separated they don't form multi-digit numbers and therefore every digit is treated as a single symbol in sorting.
There is an easier way to achieve ASCII ordering - select sort by "Expression" and use function Rank() as an ordering expression. This solution suggested by Swuehl (thank you!) in the topic Listbox - text sort not ASCII?
I modified the sorting expression to allow ordering of all values (selected as well as not active): "-Rank(Only({1} field))". Without set expression "{1}" only active records are ordered.
I made a simple example application to show both approaches to ASCII sorting (see attached ASCII_sorting.qvw).
Reloading of field's values in right ASCII order is put in separate subroutine "order_ASCII". It might be copied and used in other applications.
// Sorting a field in ASCII mode. The field will be loaded ordered in ASCII mode // Parameters: // p_field_name - a name of a field to order. Input value must be given as a string ('field name') // p_table_name - a name of a table where the field exists. Input value must be given as a string ('table name') // p_replace - recreate the field by reloading it in ASCII order: 1 - yes; 0 - no: new field will be created [$(p_field_name)_ordered] and sorted in ASCII mode SUB order_ASCII(p_field_name, p_table_name, p_replace)
See full source code of the subroutine under the spoiler below
// Sorting a field in ASCII mode. The field will be loaded ordered in ASCII mode // Parameters: // p_field_name - a name of a field to order. Input value must be given as a string ('field name') // p_table_name - a name of a table where the field exists. Input value must be given as a string ('table name') // p_replace - recreate the field by reloading it in ASCII order: 1 - yes; 0 - no: new field will be created [$(p_field_name)_ordered] and sorted in ASCII mode SUB order_ASCII(p_field_name, p_table_name, p_replace) // Loading distinct values of the ordering field __ot: LOAD Distinct [$(p_field_name)] as p, // Text value of the field to be ordered by Text([$(p_field_name)]) as p_text Resident [$(p_table_name)]; // Spliting strings into symbols __ot1: LOAD p, IterNo() as posn, Mid(p_text, IterNo(), 1) as symbol Resident __ot While IterNo() <= Len(p_text); DROP Table __ot; // Creating a text to get right ASCII ordering: concatenating symbols back into a string using "-" as a separator __ot: LOAD p, '-' & Concat(symbol, '-', posn) & '-' as p_sort Resident __ot1 Group By p; DROP Table __ot1; // Adding to the source table new field as a copy of the source field. Using the previously created separated field in ordering // We need fresh new field because the source field is already loaded in some order which we didn't change Left Join ([$(p_table_name)]) LOAD p as [$(p_field_name)], p as [$(p_field_name)_ordered] Resident __ot Order By p_sort; DROP Table __ot; // Replacing the source field by new one if needed IF p_replace = 1 THEN // Presuming that the source field is not a key (exists only in one table) DROP Field [$(p_field_name)] From [$(p_table_name)]; RENAME Field [$(p_field_name)_ordered] to [$(p_field_name)]; END IF; END SUB;
Victor.
And there is another way to achieve ASCII sorting - use expression "Aggr(Only({1} field), field)" as field and select "Text" as sort by condition. We have ASCII sorting instead of phonebook in this case. Looks like a bug but it works as needed.