Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

parsing strings 1

Status
Not open for further replies.

susanna123

Technical User
Jan 22, 2010
79
CA
Hello,

I use the left, mid and right function to parse this string but am not getting it accurate. This is the data from the field: can you help with formula?

Original: ABCDEFGHIJKLML - 12345678 - HelloWorld

The output should look like this without the word "Original" and the "-"

ABCDEFGHIJKLML
12345678
HelloWorld


 
stringvar x := {table.field};
stringvar x := "Original: ABCDEFGHIJKLML - 12345678 - HelloWorld";
stringvar array y := split(x,":");
stringvar array z := split(y[2],"-");
stringvar elem1 := "";
stringvar elem2 := "";
stringvar elem3 := "";

Then add one of the following to the end of the above formula in each of three separate formulas:

if ubound(z) >= 1 then
elem1 := trim(z[1]); //for first element

if ubound(z) >= 2 then
elem2 := trim(z[2]); //for second element

if ubound(z) >= 3 then
elem3 := trim(z[3]); //for third element

-LB
 
Hi LB,
thanks for your help.
In your formula, is it possible to not hardcode it, because the field is in that format but not necessarily those values

stringvar x := "Original: ABCDEFGHIJKLML - 12345678 - HelloWorld";
 
Sorry, I forgot to remove that line, which was just for testing. Should be:

stringvar x := {table.field};
stringvar array y := split(x,":");
stringvar array z := split(y[2],"-");
stringvar elem1 := "";
stringvar elem2 := "";
stringvar elem3 := "";

Then add one of the following to the end of the above formula in each of three separate formulas:

if ubound(z) >= 1 then
elem1 := trim(z[1]); //for first element

if ubound(z) >= 2 then
elem2 := trim(z[2]); //for second element

if ubound(z) >= 3 then
elem3 := trim(z[3]); //for third element

-LB
 
The error I get is: a subscript must be between 1 and the size of the array and it highlights

stringvar array z := split(y[2],"-");

in the formula.
 
The formula assumes that the field always starts with "Original: ". It obviously doesn't, so now you need to provide all the possible variations of what the field can look like.

-LB
 
Hi LB,
the field always starts with "Original". is correct.

my 3 formula fields look like this:
1.

stringvar x := {table.field};
stringvar array y := split(x,":");
stringvar array z := split(y[2],"-");
stringvar elem1 := "";
stringvar elem2 := "";
stringvar elem3 := "";
if ubound(z) >= 1 then
elem1 := trim(z[1]); //for first element


stringvar x := {table.field};
stringvar array y := split(x,":");
stringvar array z := split(y[2],"-");
stringvar elem1 := "";
stringvar elem2 := "";
stringvar elem3 := "";
if ubound(z) >= 2 then
elem2 := trim(z[2]); //for second element

stringvar x := {table.field};
stringvar array y := split(x,":");
stringvar array z := split(y[2],"-");
stringvar elem1 := "";
stringvar elem2 := "";
stringvar elem3 := "";
if ubound(z) >= 3 then
elem3 := trim(z[3]); //for third element

Can you tell me how i can correct this?
thks
 
Hi Lb,

The field always begins with "Original:" but anything after that may be different

See example:

Original: ABCDEFGHIJKLML - 12345678 - HelloWorld
Original: ABCDEFGDG - 2343423 - Hello
Original: AB - 3434434434 - World

Thanks again
 
If the field always contains a ":" the formula would work. I'm guessing it might be null.

stringvar x := {table.field};
stringvar array y := split(x,":");
if isnull({table.field}) or
ubound(y) = 1 then
{table.field} else
(
stringvar array z := split(y[2],"-");
stringvar elem1 := "";
stringvar elem2 := "";
stringvar elem3 := "";
if ubound(z) >= 1 then
elem1 := trim(z[1]);
if ubound(z) >= 2 then
elem2 := trim(z[2]);
if ubound(z) >= 3 then
elem3 := trim(z[3]);
elem3 //for element 3--change for other formulas
)

-LB
 
Hi LB
this is what i have for the first formula field
and is throwing me an error:


higlighting unbound(y) : a number, currency amount, boolean, date time , date time or string is expected here



stringvar x := {table.field};
stringvar array y := split(x,":");
if isnull({table.field}) or
ubound(y) = 1 then
{table.field} else
(
stringvar array z := split(y[2],"-");
stringvar elem1 := "";
stringvar elem2 := "";
stringvar elem3 := "";
if ubound(z) >= 1 then
elem1 := trim(z[1]);
 
here are my three formulas:

1st formula:

stringvar x:= {Object.Name};
stringvar array y := split(x,":");
if isnull({Object.Name}) or
unbound(y) = 1 then
{Object.Name} else
(
stringvar array z := split(y[2],"-");
stringvar elem1 :="";
stringvar elem2 :="";
stringvar elem3 :="";

if unbound(z) > = 1 then
elem1 :=trim(z[1]);

---------------------------------
2nd formula:

stringvar x:= {Object.region};
stringvar array y := split(x,":");
if isnull({Object.region}) or
unbound(y) = 1 then
{Object.Name} else
(
stringvar array z := split(y[2],"-");
stringvar elem1 :="";
stringvar elem2 :="";
stringvar elem3 :="";

if unbound(z) > = 2 then
elem2 :=trim(z[2]);

-----------------------------------
3rd formula:

stringvar x:= {Object.address};
stringvar array y := split(x,":");
if isnull({Object.address}) or
unbound(y) = 1 then
{Object.Name} else
(
stringvar array z := split(y[2],"-");
stringvar elem1 :="";
stringvar elem2 :="";
stringvar elem3 :="";

if unbound(z) > = 3 then
elem3 :=trim(z[3]);
 
You are showing three different fields here. Why? You should be using the one field that starts: "Original:". You need to check each field and see what it returns when placed in the detail section.

You are also mistakenly using "unbound"--the function is:

ubound

If you are going to use different fields with the formula, you should change ALL variables to start with "local", as in:

local stringvar x;

-LB
 
Hi LB you are right, my mistake:

here is the revised formulas:
1)
stringvar x:= {Objects.Address};
stringvar array y := split(x,":");
if isnull({Objects.Address}) or
ubound(y) = 1 then
{Objects.Address} else
(
stringvar array z := split(y[2],"-");
stringvar elem1 :="";
stringvar elem2 :="";
stringvar elem3 :="";

if ubound(z) >= 1 then
elem1 :=trim(z[1]);
)

-------------------------------

2.

stringvar x:= {Objects.Address};
stringvar array y := split(x,":");
if isnull({Objects.Address}) or
ubound(y) = 1 then
{Objects.Address} else
(
stringvar array z := split(y[2],"-");
stringvar elem1 :="";
stringvar elem2 :="";
stringvar elem3 :="";

if ubound(z) >= 2 then
elem2 :=trim(z[2]);
)

--------------------
3)

stringvar x:= {Objects.Address};
stringvar array y := split(x,":");
if isnull({Objects.Address}) or
ubound(y) = 1 then
{Objects.Address} else
(
stringvar array z := split(y[2],"-");
stringvar elem1 :="";
stringvar elem2 :="";
stringvar elem3 :="";

if ubound(z) >=3 then
elem3 :=trim(z[3]);
)
 
when running the report:

the error is produced:

stringvar array z := split(y[2],"-");

a subscript must be between 1 and the size of the array
 
I guess you can check for blanks also in each formula as in:

stringvar x:= {Objects.Address};
stringvar array y := split(x,":");
if isnull({Objects.Address}) or
[red]trim({Objects.Address}) = "" or [/red]
ubound(y) = 1 then
{Objects.Address} else
(
stringvar array z := split(y[2],"-");
stringvar elem1 :="";
stringvar elem2 :="";
stringvar elem3 :="";

if ubound(z) >=3 then
elem3 :=trim(z[3]);
)

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top