Qlik Sense Load Order Sorting

QlikView Developers have long used load order sorting to provide a custom or business rule driven sort order. Load Order sorting provides a simple way to sort values in an expected order without resorting to a numeric prefix, hidden sort fields or other techniques. Some common examples of lists commonly sorted in non-standard orders are Month Names (Jan, Feb, Mar, etc.), Grouping categories, and job titles (CEO, President, Controller, etc.).

In order to implement Load Order Sorting, the field values to be sorted must be loaded into Qlik Sense in the expected order into a temporary or permanent table. If the field is a part of another larger table, a temporary table is created to establish the sort order then the table is dropped once the larger table is loaded. The field can be loaded from any source where we can control the load order, (inline, flat file, Excel file or database). The following load script illustrates loading a single field “TitleName” using an Inline load.

TitleTemp:
LOAD * INLINE
[
TitleName
President
COO
CFO
VP, Sales
];

If we then create a Table or Filter Pane in Qlik Sense using the dimension “TitleName” the resulting table in Qlik Sense is sorted in “Auto” order by default. As we can see in Figure 1, “Auto” order defaults to standard alphabetic sorting on character strings.

Figure 1

If we look at the Sorting properties of the Job Title table it is not clear how to get this list sorted in load order as is the default in QlikView.

Figure 2

It appears that the only option is “Auto” sorting. If we disable “Auto” sorting we get a list of sorting options we can choose from. The default, “Sort alphabetically” is selected by default for a character string field as shown in Figure 3.

Figure 3

What is not apparent is that we need to unselect all of the Custom sorting options. Once all options are deselected, what remains is load order sorting as shown in Figure 4 below.

Figure 4

Now our table is sorted in Load Order as we intended.

Figure 5

I hope this was helpful in using Load Order sorting in Qlik Sense to solve this common business requirement.

,

Comments are closed.