Sorting data that are strings as integer

[mimitrem]

HI,

I want to sort data like 1,2,3,11,2B they are strings so when sorted it looks like this
1
11
2
2B
3
I would like to sort the numbers as integer and then just leave the strings as string but I just don't know how.

Could somebody help please ?

I want a result like

1
2
3
11
2B

 

[dgillz]

Create a formula field and use the val() function to convert your string to a value.

val("11&quot returns 11
Val("22b&quot returns 22

make this formula field your promary sort. You do have to display this field on your report if you do not want to. Make the actual database alphanumeric field the secondary sort.

Try that and let me know how this works. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[mimitrem]

Hi,

your solution does help but now my problem is that the values that are strings get transformed into 0

like 3B becomes 0

This is my formula if NumericText ({Volume}) then val({Volume})

but it's seems like all the data is evaluated by val even if NumericText() returns false.

 

[Ngolem]

Try this instead

1. determine the maximum size of the data (let us say 5 digits is the maximum now so make the formula based on 6 digits to take care of future numbers.
2. create a grouping formula...this formula is used only for grouping and is not displayed

@grouping

ReplicateString ("0",6 - length({table.data})) +
{table.data};

this will pad 0's to the left of the value eg

000003
00003b

This will produce a sort like this when sorted ascending

1
2
3
11
2B

hope this helps Jim Broadbent

 

[synapsevampire]

You of course don't need to Group, you can just use this formula to sort.

Nice solution, Jim.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[mimitrem]

Thanks Guys it works a lot better now

but can I do something about sorting like 9
B
C
10
and such ?

 

[Ngolem]

so you want this to be a "hex" like sort

0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,f...

you should say so in the beginning Jim Broadbent

 

[Ngolem]

I think that if you sort ascending the sort would be

09,0B,0C,10....

Simply because we sort this way in the group formula Or as a record sort and SV suggests....we don't display the results this way. Jim Broadbent

 

[synapsevampire]

This is contrary to your otiginal post:

"Thanks Guys it works a lot better now

but can I do something about sorting like 9
B
C
10
and such ?
"

Why would a B and C come after a 9 yet before a 10?

Are you saying that you only want to display the character portion of the character and number combinations?

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[mimitrem]

Ok guys

I see I haven't been clear the thing is I got all sorts of values I have integer and the in the strings I have things like 4F, F, F 4, F4, 1-D, etc. And I got to sort in some kind of order.

 

[Ngolem]

Well...you are not being fair to us by not giving us all the cases for the possible strings....and the order that you want them seen.

My "Crystal" ball only sees so much! Jim Broadbent