sorting a complex alphanumeric daa 1

[PD81]

Hello Techie frends,
I have a requirement to sort a list of data in particular order. I tried val(table.fieldname) but the order of number and alphabets are not same. I am posting some sample data which i need to be sorted. PLease help

C.211.d.2.1 Validate securities trade formats
C.211.d.2.10 Perform short sales check
C.211.d.2.11 Process cancelled trades
C.211.d.2.12 Process late trades
C.211.d.2.2 Assign JPM account codes to CFD trades
C.211.d.2.3 Assign instrument codes to securities trades with standard market identifiers
C.211.d.2.4 Set-up instruments to support CFD trades
C.211.d.2.5 Assign instrument codes to securities trades for un-listed securities
C.211.d.2.6 Assign instrument codes for time critical requirements using Fast Add
C.211.d.2.7 Assign broker details to trades

I want the above to be sorted like -C.211.d.2.1, C.211.d.2.2 (not d.2.11)

thanks a lot
regards
puneet

 

[lbass]

Try a formula like this--just for sorting:

stringvar y := {table.string};
stringvar x := split(y,".");
left(y,instrrev(y,"."))+totext(val(x[ubound(x)]),"00")

This allows for two digits at the end. If there can be more, add 0's.

-LB

 

[PD81]

Thanks LB
I am getting an error -- "This array must be subscripted"

at line --> stringvar x := split(y,".");

I may found silly but, i need this to be sorted out. Please help

-cheers
Puneet

 

[SkipVought]

but the order of number and alphabets are not same.

You do not have numbers that you are sorting. Rather, you have numeric characters that you are sorting.

Numeric characters and alpha characacters sort perfect well together and the collating order is consistent.

That is why the collating sequence is, for instance...
[tt]
1
11
111
1111
2
22
222
2222
3
33
333
3333
.......
[/tt]
This is the perfectly consistent way that numeric characters sort! They are NOT NUMBERS! You will never use this strings of digits in numeric calculations. They are IDENTIFIERS, not numeric values.

Now you posted a small sample of your data and lb posted a great solution that addresses the extent of your example. However if, for instance, you have instances like...
[tt]
C.99.d.2.3
C.211.d.10.3
C.211.d.2.101
[/tt]
then you're back to square 1.

The essential issue is, what is the MAXIMUM length that can occur in your indexing system for any string or numeric characters?
[tt]
X.iii.Y.jjj.kkk
[/tt]
Max Len i=3 ???
Max len j=1 ???
Max len k=2 ???





Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lbass]

That should have been:

stringvar y := {table.string};
stringvar [red]array[/red] x := split(y,".");
left(y,instrrev(y,"."))+totext(val(x[ubound(x)]),"00")

But Skip is correct. You should respond to the issues he raised.

-LB