Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: sorting on alpha-numeric field Post Reply Post New Topic
Page  of 2 Next >>
Author Message
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Topic: sorting on alpha-numeric field
    Posted: 28 May 2009 at 5:55am
Hi all,
 
I have a report where there are order numbers in alpha-numeric pattern. This report is run against a number of institutions where there are inconsistent coding of  order number being adopted:
WB123
NBIT234
 
I cannot use normal ascending order from Recort Sort Expert
because WB13 will be after WB123.
 
I created a formula @sort_order_number where:
val(replace({LIX.ID},left({LIX.ID},2),""))
{LIX.ID}is order number field. WB, NBIT are institution prefix.
 
But it only handles order number with two prefix. Could someone advise if it's possible to make the formula flexible to handle >= 2 prefix? or it can also sort order number properly without prefix?
Thanks in advance.
 
John


Edited by johnwsun - 28 May 2009 at 6:00am
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 28 May 2009 at 6:31am
Hi
 
What is the pattern i.e. will the numbers always fall  on right hand side?,can you post few more sample records to play with....
 
cheers
Rahul
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 28 May 2009 at 6:35am
I would use a loop something like:
local numbervar i;
 
for i := 1 to len({LIX.ID}) Do
( if isNumeric(mid({LIX.ID}, i)) then
    exit for
)
 
then continue with your formula, this should strip the leading alpha characters.  If they use alpha later on, this will always fail.
 
HTH
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 28 May 2009 at 6:41am
Hi
use the code below in your SQL
 
Left(SubString(LIX.ID, PatIndex('%[0-9.-]%', LIX.ID), 8000), PatIndex('%[^0-9.-]%', SubString(LIX.ID, PatIndex('%[0-9.-]%', LIX.ID), 8000) + 'X')-1)  AS 'Sort on LX ID'

the above code will extract just the number part from the character string,once done you can use the above field to sort the records.

Cheers
Rahul
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 28 May 2009 at 6:50am
Thank you all,
 
I would like to use lockwelle's advice to begin with.
 
Hi Lockwelle,
 How do I continue use my fromula from your for loop? Please advise...
 
John


Edited by johnwsun - 28 May 2009 at 7:05am
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 28 May 2009 at 7:40am
Rahul,
Hadn't thought of regular expressions in Crystal...I will have to keep that in mind. Thanks again.
John
local numbervar i;
 
for i := 1 to len({LIX.ID}) Do
( if isNumeric(mid({LIX.ID}, i)) then
    exit for
);
 
mid({LIX.ID}, i)
 
having not actually done this, but the above should be all you need to update you existing formula.  I would probably comment out the existing code and add this.  Hopefully it will work as desired.
 
If not try Rahul's suggestion.  Should just need to drop it in the same formula as his is returning the numeric portion as well, just using a different technique.
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 28 May 2009 at 7:56am

Thanks, lockwelle,

It doens't work.
 
Hi Rahul, is your approach applied on SQL database on in the Crysatal Report itself?
 
J
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 28 May 2009 at 8:04am
Hi lockwelle,
 
It works now after I put Val in front of
mid({LIX.ID}, i)
so Val(mid(({LIX.ID}, i))
Thanks a lot!
 
J


Edited by johnwsun - 28 May 2009 at 8:05am
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 29 May 2009 at 4:47am
Hi John
 
My approach is in SQL SELECT STATEMENT.....then using view or stored procedure to get the results and use in the report.
 
Cheers
Rahul
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 30 May 2009 at 6:41am
Hi
I would like to revisit this sort problem
with the below:
WB123
WB234
WB33
 
the formula @sort_orderID
 
for i := 1 to len({LIX.ID}) Do
( if isNumeric(mid({LIX.ID}, i)) then
    exit for
);
 
val(mid({LIX.ID}, i))
 
can handle the sort properly.
However, when the ID has suffix, such as 134WBIT, 135WBIT, 23WBIT (institutions have order id entered inconsistently), the above formula fails. If I changed sort by using CR's normal record sort instead of formula, then it works for ID with suffix.
Lockwelle, could you please advise some improvement for this formula.
Rahul, when I tried to create a view with your SQL, there is error on SQL database.
Select Left(SubString(LIX.ID, PatIndex('%[0-9.-]%', LIX.ID), 8000), PatIndex('%[^0-9.-]%', SubString(LIX.ID, PatIndex('%[0-9.-]%', LIX.ID), 8000) + 'X')-1)  AS 'Sort on LX ID'
Please advise, thank you all.
 
John


Edited by johnwsun - 30 May 2009 at 6:43am
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.