| Author |
Message |
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
   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 Logged |
|
|
rahulwalawalkar
Senior Member
Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
|
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 Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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 Logged |
|
rahulwalawalkar
Senior Member
Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
|
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 Logged |
|
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
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 Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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 Logged |
|
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
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 Logged |
|
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
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 Logged |
|
rahulwalawalkar
Senior Member
Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
|
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 Logged |
|
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
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 Logged |
|
|
|
