How to sort a TEXT field so numbers are in numeric order 3

[sharkatek]

I have a text field with these (sample) values and they must be sorted. The default sort leaves me with something like this:

BT01
CM201
10
100
10005
1001
101
1042

Is there any way to force the actual numbers to appear at the top in true numeric order followed by the values that start with letters?
Thank-you in advance.
Sharkatek

 

[KellyK]

Can you add a calculated field where all that's stored is a "1" if the text field starts with a number or a "2" if the text field starts with an alpha? Then you can do a primary sort on the new field and a secondary sort on the original text field you have listed.

Kelly

 

[sharkatek]

Thank-you, Kelly - this is helpful to a point. I should have indicated that sometimes the field has a dash in it:
25-43

and sometimes has a trailing letter:
3452T

Since it is a 10 character field, I had already done something similar to your suggestion but it required looking at ALL characters, then creating not just one "dummy" field, but a second "dummy" field for those values that were strictly numeric. The second field contains a character to numeric converted value. I sort first on the first dummy field, second on the second dummy field, thirdly on the actual field itself. I had to have the third sort since conversion of alphanumeric fields to numeric (when letters are present) I got a corrupted value and they were all the same.

Thanks for your help though!
Sharkatek

 

[griffindm]

Shark,

It looks like your data has no consistency in format. What you are trying to achieve (I think) is to change the data so that any numeric portion is zero padded to make it sort properly for the numeric portion of the string as well as the character. Thus '10' becomes '0010' and '100' becomes '0100', which allows them to be sorted properly as a string. As the size of the padded numeric portion of the string is indeterminate, it will be difficult, possibly impossible to make an expression that parses the full string into character and numerica components, then left zero pads the numeric to fill the unused portion of the full string. The logical impossibility revolves around the numerics with a dash between them. Which side do you pad? If both, how do you split the available extra characters of the 10 character string?

If you can make a rule for the dash numerics, you may be able to create a UDF database function to handle it and use it within Impromptu, but it is NOT an exercise for the faint of heart.

Regards,

Dave Griffin




The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[DoubleD]

Dave,
Couldn't you use a combination of Instring (not sure what the equivalent is in Cognos off the top of my head) and Length functions to identify the location of the hyphen, as well as the required padding. Oh, it would be painful, but seems to be possible.

I echo Griffindm's concerns regarding the consistency of data. Seems like there is a design flaw in the data structure. This should be corrected on the Database side, not through data manipulation.

I am what I am based on the decisions I have made.

DoubleD

 

[sharkatek]

Thanks to all you gurus who have tried to help me. Fortunately for me, the dash was always in the third character, so it was just one instring statement. It was worth the time to build a dedicated report which I turned into a hot file because it is a sort that is needed on just about every report I run. I was just hoping there was SOME easier way!