Parsing data 1

[philly212]

I need some help. Here's my example of some data.
10 BK 5 CLR 2210 RED
2199 BK 10 RED 55415 CLR
The format is number space color, etc.
I had it all parsed out but was then told the number may be more than 2 digits, so I had to throw that code out. I'm creating formulas for each color so I can do some sums.
Ie. for color CLR, I can parse the data(if any) to the right of the color, but im having trouble parsing the data on the left of the color. below is the example code for CLR color.

//masterclear would be the data, if clr exists then....
if Instr(masterclear,"CLR")>0 then left(masterclear,(instr(masterclear,"CLR")+2))
which gives: 10 BK 5 CLR or 2199 BK 10 RED 55415 CLR
but I dont know how to parse the left side.

Any ideas?

 

[Turkbear]

Hi,
Use the MID and Len and ABS functions to parse the left side up to CLR:
(Parens separated for clarity, but need not be - also check them some may be unmatched)

if Instr(masterclear,"CLR")>0 then 
MID(masterclear,1,
ABS(
(instr(masterclear,"CLR")- Len(masterclear))
) [COLOR=green]//will result in a negative number for the length of string to extract, the ABS function will convert it[/color])
)

should give
10 BK 5
or
2199 BK 10 RED 55415



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[philly212]

I apologize but I failed to mention that I need the number before the color, or the number and the color.

 

[Turkbear]

Hi,
Just those, like

10

or

10 RED



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[philly212]

Yes. The problem I am getting is the number can be a single digit to 5 digits, and the order the colors are in are never the same.

 

[lbass]

For your initial sample:

10 BK 5 CLR 2210 RED
2199 BK 10 RED 55415 CLR

...what are the results you expect to see?

Are there multiple colors per string? It looks like each string has both "CLR" (clear) and "red", and "BK" could be black for all we know.

-LB

 

[philly212]

Yes, multiple colors per string. There's probably about 10 different colors. Sometimes only 1 color is listed, sometimes all 10.
I would hope to see an output like "10 BK" for the black formula, and a "5 CLR" for the clear formula. The data is always in the format "# space color" then space "# space color", etc.
I tried using the unbound to give me the number of spaces thinking I could filter out based on spaces but I can't figure it out.

 

[lbass]

Create this formula for the detail section to do the accumulation:

whileprintingrecords;
stringvar array x := split({table.masterclear}," ");
stringvar array y;
numbervar bk;
numbervar clr;
numbervar rd;
numbervar i;
numbervar j := ubound(x);
numbervar k := 0;
for i := 1 to j step 2 do(
k := k + 1;
redim preserve x[j];
redim preserve y[k];
y[k] := x+" "+x[i+1]
);
for k := 1 to j/2 do(
stringvar array z := split(y[k]," ");
if "BK" in y[k] then
bk := bk + val(z[1]);
if "CLR" in y[k] then
clr := clr + val(z[1]);
if "RED" in y[k] then
rd := rd + val(z[1])
);

Then you can use formulas like these in the report footer to show the results:

//{@black}:
whileprintingrecords;
numbervar bk;

//{@red}:
whileprintingrecords;
numbervar rd;

//{@clear}:
whileprintingrecords;
numbervar clr;

-LB

 

[philly212]

LB. The formula seems to return 0's. What does the "redim preserve" and this "y[k] := x+" "+x[i+1]" do?

 

[lbass]

The first formula is meant to be suppressed. You need to check the values of the display formulas.

-LB

 

[philly212]

It's working good. Is there an additional thing I can add to fix any "a subscript must be between 1 and the size of the array"? on y[k] := x+" "+x[i+1]

 

[lbass]

I think then your field can either be null or you have some instances where there are no spaces. Please take a look at your data and report back--can it be null? If there are no spaces, what does the field look like?

-LB

 

[philly212]

I did find some bad data that was messing up the formula. It seems once I process all my data, I still have some inconsistently formatted data. Is there a line I can comment out or a line of code I can add for troubleshooting?

 

[lbass]

Please explain and show examples of bad data and also answer my question about nulls.

-LB

 

[philly212]

There are no nulls. Some of the data might look like this:
1 BK (FREE)
4 BK FREE PAIR
1 OR (FREE - 2 PER DZ!)

 

[philly212]

I did find a "1 BK 1RED 1 BLU" that gave an error in crystal. I would want to flag the "1RED" and get it fixed on the data side.

 

[philly212]

Is there a way to ignore that subscript error and just get rid of that particular line that it is testing? Id prefer this over anything else.

 

[lbass]

Try something like this (didn't test):

whileprintingrecords;
stringvar array x := split({table.masterclear}," ");
stringvar array y;
numbervar bk;
numbervar clr;
numbervar rd;
numbervar i;
numbervar j := ubound(x);
numbervar k := 0;
[red]if remainder(j,2) = 0 then( [/red]
for i := 1 to j step 2 do(
k := k + 1;
redim preserve x[j];
redim preserve y[k];
[red]if isnumeric(x[1]) then [/red]
y[k] := x+" "+x[i+1]
)[red]) else
"Bad Data"[/red]
;
[red]if remainder(j,2) = 0 then( [/red]
for k := 1 to j/2 do(
stringvar array z := split(y[k]," ");
[red]if isnumeric(z[1]) then ([/red]
if "BK" in y[k] then
bk := bk + val(z[1]);
if "CLR" in y[k] then
clr := clr + val(z[1]);
if "RED" in y[k] then
rd := rd + val(z[1])
)[red])) else
"Bad Data"[/red];

-LB

 

[philly212]

LB. Thanks, that helped a lot. I just had to change the else result to a boolean.

 

[philly212]

Last question on this. Is there a way to summarize the totals by groups instead of having to put it in the report footer?