Print Page | Close Window

sorting on alpha-numeric field

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=6497
Printed Date: 03 Dec 2022 at 3:57am


Topic: sorting on alpha-numeric field
Posted By: johnwsun
Subject: sorting on alpha-numeric field
Date 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



Replies:
Posted By: rahulwalawalkar
Date 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


Posted By: lockwelle
Date 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


Posted By: rahulwalawalkar
Date 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


Posted By: johnwsun
Date 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


Posted By: lockwelle
Date 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.


Posted By: johnwsun
Date 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


Posted By: johnwsun
Date 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


Posted By: rahulwalawalkar
Date 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


Posted By: johnwsun
Date 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


Posted By: DBlank
Date Posted: 30 May 2009 at 8:35pm

JOhn,

Can you add an if statement to this so if the last character is not numeric then put in 0 else put in your other formula Then do a primary sort on this formula field and a secondary sort on the field itself?
All your items that end in a non numeric value will be listed first as a 0 as the primary sort, then these zero sorted items should sort correctly using the secondary sorting of the original field.


Posted By: johnwsun
Date Posted: 30 May 2009 at 10:19pm
Hi DBlank,
 
I have borrowed a formula from Issue 57 of Crystal Clear( News and Hints for Cryatl Reports) by Chelsea Technologies:
 
@sort_order_num
 
local numbervar digit;
local numbervar v;
local stringvar s;
local numbervar i;
v :=0;
for i := 1 to len({LIX.ID}) do (
   s:= mid({LIX.ID},i,1);
   
if isnumeric(s) then
   digit := val(s);
    v:= v*10 + digit;
);
 
v
This works for both prefix and suffix.
 
John
 


Posted By: Rasta
Date Posted: 13 Jul 2009 at 2:22am
Hello Everyone. I am trying to sort data in a report and one of the problems I am facing is alphanumeric room numbers. eg 01A, 01B, 02A ... and 01.1, 01.2, 02.1, 02.2....
I was wondering if some one could help me because the normal ascending order of sorting a group does not help.

Your help will be a big help and I would like to thank you in advance.

Cheers


-------------
SAR


Posted By: johnwsun
Date Posted: 13 Jul 2009 at 5:15am
Hi SAR,
 
Have you tried the following lines:
local numbervar digit;
local numbervar v;
local stringvar s;
local numbervar i;
v :=0;
for i := 1 to len({LIX.ID}) do (
   s:= mid({LIX.ID},i,1);
   
if isnumeric(s) then
   digit := val(s);
    v:= v*10 + digit;
);
 
v
 
John


Posted By: Rasta
Date Posted: 14 Jul 2009 at 1:51pm
Thank you for replying John. I am trying to use the formula that you have given. I needed the report as there was no other way of making work easier and so I added another group which was the room number and then sorted it according to specified list. BUT this means I have numerous reports, one for each facility. I was able to do that as the room numbers are fixed.

I will try this and let you know.. If I get one report then I can replace the .rpt file the database application is using and I can use it through the software.. Now I have to run it through logicity application builder.

Thanks John


-------------
SAR



Print Page | Close Window