Custom Sorting

[vaka]

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!

[rubenmarin]

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"

[Vegar]

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]