topic Re: Custom Sorting in App Development

Custom Sorting

vaka — Mon, 06 Oct 2025 15:57:16 GMT

Hi all, I am trying to do custom sorting. I have a Master Dimension that have values like:
1 - flp,e2e
10 - Prod&Dev E2E
2 - T41
4 - Security
121 - DataIntegrity
123 - Improvement
Not Ranked - TSA
Not Ranked - PIB
Unallocated

and many more in this format

I am trying to sort by this dimension in a straight table, so that it will take the beginning number to sort
the required sort will be in the below format:
1 - flp,e2e
2 - T41
4 - Security
10 - Prod&Dev E2E
121 - DataIntegrity
123 - Improvement
Not Ranked - TSA
Not Ranked - PIB
Unallocated

When I am using sorting numerically, its not working
and when i use alphabetical sort, its giving result like below:
1 - flp,e2e
10 - Prod&Dev E2E
121 - DataIntegrity
123 - Improvement
2 - T41
4 - Security
Not Ranked - TSA
Not Ranked - PIB
Unallocated

Please help me resolve this so that I can sort based on the starting numbers before '-'
Thank You!

Re: Custom Sorting

rubenmarin — Mon, 06 Oct 2025 18:34:50 GMT

Hi, if I load those values on QV, the default sort for numbers (using text sort) is the one you expect.

You can also try this expression:

Alt(Num(SubField(FieldName,'-',1)),99999)

But the last 3 values don't follow any order, as a trick you can add an inline table before the values are loaded, in example:

SortValues: LOAD * Inline [ FieldName Not Ranked - TSA Not Ranked - PIB Unallocated ];

The you can sort by expression and the by load order, as this 3 bvalues will have 9999 as sort, the will use the load value, and the inline table will give the sort order of those values.

After loading all values use a "DROP Table SortValues"

Re: Custom Sorting

Vegar — Tue, 07 Oct 2025 08:27:50 GMT

Another solution could be to make a dual out of your Field. Building on @rubenmarin 's alt(...) function.

Declare the FieldName like this in the script. Make sure that the constant (in this case 1000) is higher than any of the ids found on the field values (in your example the highest id is 123)

dual( [FieldName], alt(num( SubField([FieldName],'-',1)), 1000+ AutoNumber([FieldName])) ) as [FieldName]