Creating a custom sort using load order
In building applications, a field is often encountered that does not natually sort in the desired order, alphabetically or numerically. The most frequent occurrence of this I have seen is groupings with values such as ( <10 | 10-20 | 20-30 | >30 ).
One of the sorting options for a field is Load Order, which captures the order in which the distinct values of a field were first introduced into the application. The first value loaded gets an index of 1, the second unique value gets a 2, and so on. The field index of any particular slice of a dimension can be found using the FieldIndex function. For example:
FieldIndex('Size', 'a) <10') = 8
This load order can be forced using an inline load at the beginning of your script, before you load your main reporting data. This sets the field value indexes in the order you choose to list the items because you are controlling the order in which they are first introduced into the application.
NB: The field name in this table (“Segment”, below) must precisely match the ultimate field name in the reporting data you wish to sort.
LOAD * INLINE [
Then, at the end of the script, after the rest of your reporting data has been loaded, drop the table, as it is not needed in your final data model.
DROP Table SegmentSort;
The field can now be sorted by Load Order to display the field values in the desired order.
There's another option that involves using a mapping table to assign numeric values to each field value using the dual function, allowing you to sort numerically, which has its own advantage and disadvantage.
|Force load order||Virtually no impact on reload time||Must be in the script of each front end application that needs the sort|
|Mapping table/dual||Can be done once in transform QVD layer||ApplyMap adds to reload time|