Ordering fields in ASCII mode

vicn1390 — Wed, 16 Jan 2019 10:19:24 GMT

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.

Re: Ordering fields in ASCII mode

vicn1390 — Wed, 16 Jan 2019 10:30:46 GMT

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.

topic Ordering fields in ASCII mode in QlikView

Ordering fields in ASCII mode

Re: Ordering fields in ASCII mode