Solved

Split and Trim Formula Help in Crystal Reports

Posted on 2013-01-17
8
4,020 Views
Last Modified: 2013-01-22
On an earler question, mlmcc helped me to split some information in a report.  I'm trying to use the same formula in a similar report and come across some errors.  

I have a list of part numbers and descriptions.  They describe a thread diameter and length.  My formula is as follows:
Local StringVar strForValue;
Local StringVar Array strLengthList;
Local StringVar Array strFractionList;

strForValue := Trim(Split(Split({p21_view_inv_mast.item_desc}," X ", -1,1)[2]," ")[1]);

strForValue := Replace(strForValue,'"','');
strForValue := Replace(strForValue,'-',' ');

strLengthList := Split(strForValue,' ');
If UBound(strLengthList) = 1 then
   Val(strLengthList[1])
Else
(  
   strFractionList := Split(strLengthList[2],'/');
   Val(strLengthList[1]) + Val(strFractionList[1]) / Val(strFractionList[2]);
)

I will get an error in the formula in a couple of different places.  One would highlight the very last phrase (strFractionList [2]);  and another would highlight up in the beginning of the formula where it says {p21_view_inv_mast.item_desc}," X ", -1,1)[2]," ")[1]);

First off:  Could someone explain to me what this formula is doing?  Maybe then I could figure out which "Descriptions" in my data it does not like.  

Secondly:  Does it matter if the description has a lower case "x" eventhough the formula calls for an uppercase "X"?  

If I understood the formula I may be able to change the descripton on some of these parts.

What I end up doing is running the report and going page by page until it causes an alert then I look at the page it stopped on and try to find funky looking descriptions and either filter them out of edit the description.

For instance, I know that it bombs out when it reads this description:  1 1/2-8(UNJ)X63.5DE STUD 4340 COLD ROLL  but if I put a space before and after the "X" it will read fine.  So I understand the it requires this but I'm not sure of anything else that I have to have.

I have figured out that the formula doesn't like when my description is in feet and inches.  For example  5/8-11 X 20'-0" DBL END STUD 4 &10 TOE would bomb.  So is there a way to include this in my formula?

Naturally, it will also bomb if there is not a diameter and length like in "5" SUPPORT STUD" so I am filtering them out individually.  Again, if there is a way to eliminate this in the formula that would be great because now I have to go page my page to try and find one of these.  Currently my report is only for fiscal year 2013 which start 10/1/2012 so I don't have a lot of data.  But I want to remove this filter and have several years worth of data so going page by page won't be an option.
DE-History-for-Mike.rpt
0
Comment
Question by:ITworks
  • 3
  • 3
  • 2
8 Comments
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 125 total points
ID: 38790207
Trim(Split(Split({p21_view_inv_mast.item_desc}," X ", -1,1)[2]," ")[1]);

Split({p21_view_inv_mast.item_desc}," X ", -1,1)[2]
This splits the string into parts using the ' X ' as the split term.  Notice the spaces around the X.  That is why you have a problem if the spaces aren't there.  The -1 says to return all substrings.  The 1 says use a comparison that is NOT case sensitive so an x or X will match.

It then returns the second string found.

The returned string is then split on the spaces and the first part returned which is then trimmed of any spaces it might have at the start or end.

Replace(strForValue,'"','')
Replaces the inch " notation with nothing

Replace(strForValue,'-',' ');
Replaces the DASH - with nothing

You can replace ' with
Replace(strForValue,"'",' ');

Can you provide a good sample of the data in a spreadsheet.  Just need that field.
Also would be helpful to provide the value you want to return from the formula

ALso explain what (UNJ) means and does it affect the value?

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38790288
If you think it would help, add a link to the previus question.  I can't find it.

mlmcc
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 375 total points
ID: 38791987
mlmcc,

 FWIW, I think this is it.

http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_27400301.html

 I found it by searching for one of the variable names.  :-)


 ITworks,

 You're probably going to need to be able to define what the data can look like (it has these characters or those characters, in whatever combinations or patterns) and how you want to handle each situation.

 Also, I have a slight correction to mlmcc's post.  He said that Replace(strForValue,'-',' ') replaces the dash with nothing, but it's actually replacing it with a space.  And then the string is split at the space.  So, for example, if you had "1-2" after the " X ", the "1-2" would be changed to "1 2", and then split into "1" and "2".

 The problem with "5/8-11 X 20'-0" DBL END STUD 4 &10 TOE" is that the string is split at the " X " to get:
20'-0" DBL END STUD 4 &10 TOE

 That is split at the spaces and the first part is kept, which is:
20'-0"

 The double-quote is removed and the "-" replaced with a space, and then that's split at the space, giving you two strings:

"20'" and "0"

 The single-quote is still after the 20, which is fine, because the Val function will ignore it.

 I believe the error is because at this point the formula sees that there are two strings ("20'" and "0") and assumes that the second one is a fraction and contains a "/", so it splits the second string ("0") using "/" as a delimiter, and gets an error when it tries to use the part after the "/", because there is no "/".

 You could avoid the error by using UBound to make sure that strFractionList has more than one element (so the formula won't try to use the part after the "/" when there is no "/"), but the real question is, what do you want to see from a description like "5/8-11 X 20'-0" DBL END STUD 4 &10 TOE" ?

 James
0
 
LVL 4

Author Comment

by:ITworks
ID: 38793334
My main goal is to filter out parts that are less than 2-1/8" diameter and 24" in length.

So I need to display  the diameter and length of these parts.  The diameter will always the first part of the description but as I have found out there are a very small number of cases that the length was not part of the description (there was not an "X" in the description to separate the two)

Sometimes the person will enter something different like "UNJ".  This is a type of thread, different than normal threads.  There are other instances where someone put in [2A], which is another type of thread call out like in the description 5/8-11[2A] X 4"

Would it be possible to put a statement in the formula saying that if it doesn't have a lone "X" to filter those out?  Since it will be a minimal amount of those parts in this report I can live with that.  

The report is being used to find out which jobs we could transfer to another machine if we purchased the machine.  So if a few stragglers are left behind it's not going to be a major deal.

I've attached an Excel spreadsheet with a small sampling of part descriptons and what I would like to see for my LENGTH.
What-I-want.xls
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 34

Assisted Solution

by:James0628
James0628 earned 375 total points
ID: 38796003
The old formula is trying to convert the length into a number.  For example, if you had "5 X 2-1/2", you'd get the number 2.5.

 Do you actually need that, or do you just need to see the part after the " X ", and then you can interpret it for yourself?  Extracting the part of the field that's after the " X " is easy.  It's trying to interpret it and convert it into a number that's tricky, because of the different formats.

 Also, looking at your sample data, the old formula doesn't handle cases where the length includes feet and inches.  It takes the part after the " X ", up to the first space, and tries to convert that to a number (including a possible fraction).  It's looking for something like my example above, "5 X 2-1/2".  But with a description like '1 1/2-6 X 2' 11-1/4" SINGLE END STUD 4"', the part between " X " and the first space is just "2'", so you'd just get the number 2.  The formula completely ignores the '11-1/4"' part.

 If you really want to convert the lengths into a number, that will need to be addressed.  Then there would be the question of what kinds of units you'd use?  For example, what would 2' 11-1/4" be?  2.94 feet?  35.25 inches?

 If it's OK to just see the part of the description that's after the " X ", without trying to convert it into a number, you can just use:

Split ({p21_view_inv_mast.item_desc}," X ", -1, 1)[2]

 That will just show you everything after the " X " or " x ".  Normally you'd want to check to make sure that the field includes " X ", to avoid getting a subscript error, but if you use the record selection formula below, then the report will only include those records, so that won't be a problem.


 As for your question about only including the descriptions that have a single " X ", you could use a record selection formula like this:

UpperCase ({p21_view_inv_mast.item_desc}) like "* X *" and
not (UpperCase ({p21_view_inv_mast.item_desc}) like "* X * X *")

 That says to only include the records that include " X ", but not if they include it twice (or more).  I used UpperCase to also include any records with " x ", instead of " X ".

 Of course this is assuming that you won't have " X " somewhere else in your description.  For example, let's say that you had some kind of multi-pack that included 5 of something and the description was something like "1 1/2-6 X 12 B7 STUD X 5".  That would _not_ be included on the report, because of the second " X " at the end.

 James
0
 
LVL 4

Author Comment

by:ITworks
ID: 38801103
I guess I don't really have to have it convert everything into a number.  I could do this in Excel as long as I have the info, meaning if it's in feet and inches I would need to know that.

If I get to choose, I'd like to have everything converted to inches.

I used this formula like you said James,  Split ({p21_view_inv_mast.item_desc}," X ", -1, 1)[2]
and wondered if now we could just make another formula to split everything up to a letter.  I just don't know how to write that formula.
0
 
LVL 34

Accepted Solution

by:
James0628 earned 375 total points
ID: 38804512
Looking for the first position for one of a group of characters (eg. letters) is trickier.  The only way I know to do that is to loop through the characters and check them.

 Assuming that you're using a record selection formula like the one from my last post, so that you know that every record will include " X ", the following should give you everything after the first " X " and before the first letter (in upper or lower case) that comes after that:

Local StringVar str;
Local NumberVar i;

str := Split ({p21_view_inv_mast.item_desc}," X ", -1, 1)[2];

i := 1;
while i <= Length (str) and not (UpperCase (str [ i ]) in "A" to "Z") do
  i := i + 1;

Left (str, i - 1)

Open in new window

0
 
LVL 4

Author Closing Comment

by:ITworks
ID: 38805274
Thanks to both of you, AGAIN, for your help.

Sue
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now