We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Crystal Reports: How do I return the position in a deliminated string

Medium Priority
678 Views
Last Modified: 2012-05-06
I'm trying to build some new reporting, and have hit a road block. Below is an example of the data I'm working with:

Field 1: TC5485,UR9283,TY_IN8097,PO9234
Field 2: OK,OK,OK,ERROR

As you can see, each value is deliminated by a comma (,). I want to be able to extract all UR9283 values, and their associated vcode in field 2. I was thinking if I could find out what position in the array that the UR value was, I could use this to extract the position in the field two. However it can't be the position as in number or characters from the left as it will not always be OK, OK.

I have been able to extract the values from the first field by using:

IF InStr({Field 1},'UR') = 0 THEN {@Null} ELSE
MID({Field 1},(InStr({Field 1},'UR')),6)

This pulls out all the URxxxx strings, now I need to grab the associated value from Field 2.

Any advice? It can be in a completely seperate field to this value that I have extracted.
Comment
Watch Question

Author

Commented:
Sorry, I should say that these values are actually stored as a string, not array.

Commented:
Is the 'UR' string always found as the second field of the comma-delimited string?  If it is, then the following formula will work (using BASIC syntax):
dim x as string

x = mid ({@Field 2},(InStr({@Field 2},",")) + 1,2)

if trim(x) = "OK" then formula =  "OK" else formula = "Error"
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
Try it this way

Local stringvar array splitstr;
Local NumberVar i;

IF InStr({Field 1},'UR') = 0 THEN
    {@Null}
ELSE
(
    splitstr := Split(Field 1},',');
    for i := 1 to UBound(splitstr) do
         if left(splitstr[i],2) = 'UR' then
             split({field2},',')[i]
)

If the {@Null} doesn't work use ''

mlmcc

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Commented:
Minor typo correction to mlmcc's formula:

    splitstr := Split(Field 1},',');

  should be

    splitstr := Split({Field 1},',');


 However, that formula doesn't actually work.  At least not under CR 10.  The last part just produces a True result (a boolean value).  Unless {@Null}  is a boolean, the formula won't even run.  If I remove the IF-ELSE and just leave the stuff after ELSE, I just get True as a result (whether UR is in the target string or not).

 This can be fixed by assigning the result of split({field2},',')[i] to a variable and outputting that variable after the for loop, but I have a couple of questions that may affect how, or if, this works.

 Do Field1 and Field2 always have the same number of elements?

 Do any of the elements ever contain a comma, or do they only appear as delimiters?

 Will there be any elements in Field1 that start with UR that you do _not_ want to include?

 Can you have more than one UR element in Field1?  If so, how do you want to handle that?  This could change things drastically.

 James

Author

Commented:
Thanks guys, I actually split my head open, and eventually came to the same result! Amazing what you can do when you try, I just thought there may have been something easier. Appreciate the help, both got me there.
CERTIFIED EXPERT

Commented:
You're welcome.  Glad I could help.

 James
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.