Processing Semi-colon separated values 1

[Halfcan]

Ok, Heres a tough one.

I have a string with multiple values separated by semi-colons.
example: 0100;0200;0300;AA;BB;C1C;D0D;EE;ZZ;YY00;999;777

The number of semi-colon separated values in this string will always vary.

There values follow NO particular order.

The character length of the values can vary.

One constant, each value will only occur 1 time in the string per record.
example: This is NOT possible: 0100;00;0100;....

This data is coming from a Multi-Select type pulldown field.

What I'm attempting to do, is to pull out each individual value, and report on how many times it occurred in the report.

Example: 0100 occurred 20 times.
AA occurred 6 times.

Is this even possible?
I hope this makes sense....

Thanks,
HC

 

[vidru]

Two formulas, using dynamic arrays:

//@AddToArrays
//This would go in your Details section, or wherever
//  it is that you're getting this field.
WhilePrintingRecords;
StringVar Array strArry;
NumberVar Array numArry;
StringVar Array tmpArry := Split({Table.Field},";");
NumberVar i;
NumberVar j;

For i := 1 to UBound(tmpArry) do(
  if not (tmpArry[i] in strArry) then
    (Redim Preserve strArry[UBound(strArry) + 1];
     strArry[UBound(strArry)] := tmpArry[i];
     Redim Preserve numArry[UBound(strArry)]; 
     numArry[UBound(numArry)] := 1;
     "")
  else 
    (For j := 1 to UBound(strArry) do
       (if tmpArry[i] = strArry[j] then
          numArry[j] := numArry[j] + 1;
        );
     "");
);
"";

//@DisplayResults
//Put this in the Report Footer, make it wide enough, 
//  and format it to 'Can Grow'
WhilePrintingRecords;
StringVar Array strArry;
NumberVar Array numArry;
NumberVar i;
StringVar Array Output;
Redim Output[UBound(strArry)];

For i := 1 to UBound(strArry) do(
  if numArry[i] = 1 then
    Output[i] := strArry[i] + " occurred 1 time."
  Else
    Output[i] := strArry[i] + " occurred " + ToText(numArry[i],0) + " times."
);

Join(Output, chr(13));

-dave

 

[Halfcan]

Dave, this is great. thank you.

The Only thing I'm not sure about, is how I'm getting this first line,...oh well, I'll mess with it..

occurred 0 times.
0100 Ping occurred 5 times.
0300 Optical occurred 4 times.
0200 LinkDown occurred 1 time.
0500 DNS occurred 1 time.
0700 SMTP occurred 1 time.
0900 IMAP occurred 1 time.
Thanks,

HC

 

[vidru]

I know what the problem is... the array always starts putting data in element 2. Replace the @AddToArry formula with this one.

//@AddToArrays
//This would go in your Details section, or wherever
//  it is that you're getting this field.
WhilePrintingRecords;
StringVar Array strArry;
NumberVar Array numArry;
StringVar Array tmpArry := Split({Table.Field},";");
NumberVar i;
NumberVar j;
BooleanVar blnArrayDimmed;

For i := 1 to UBound(tmpArry) do(
  if not (tmpArry[i] in strArry) then
    ( if blnArrayDimmed then(
        Redim Preserve strArry[UBound(strArry) + 1];
        Redim Preserve numArry[UBound(strArry)];
        "")
      else(
        Redim strArry[1];
        Redim numArry[1];
        blnArrayDimmed := true;
        "");
      strArry[UBound(strArry)] := tmpArry[i];
      numArry[UBound(numArry)] := 1;
     "")
  else
    (For j := 1 to UBound(strArry) do
       (if tmpArry[i] = strArry[j] then
          numArry[j] := numArry[j] + 1;
        );
     "");
);
"";

-dave

 

[Halfcan]

thats it. Thanks!

 

[Halfcan]

Hi Vidru,

I have a small problem.

Do you know how I can split these calculations up between groups? The totals just accumulate from the top - down without regard for any grouping. Is there a way to reset the Add to array forumla on the change of a group?
Thanks again for your help. I really appreciate it.

HC

 

[vidru]

Add a new formula and put it in your Group Header:

//@Reset
WhilePrintingRecords;
BooleanVar blnArrayDimmed := false;

If you're using the 'Repeat Group Header on Each Page' option for your Group Header, then you'll need to change that up a bit:

//@Reset
WhilePrintingRecords;
BooleanVar blnArrayDimmed;
If InRepeatedGroupHeader then
blnArrayDimmed := true
else
blnArrayDimmed := false;

-dave

 

[Halfcan]

Thanks Dave,

I'm not Repeating Group headers on Each page, but I tried both forumlas anyway.

Both formulas always return false, and the Display Results formula still adds up all groups together as it moves down the report. no change.

This is were I have the formulas:

Reset: group header #1a ( I only have 1 group)
AddtoArray: Details section
Display Results: group footer #1a

any other ideas?

Thanks,HC

 

[vidru]

OK then, let's reset the arrays in the @Reset formula as well:

//@Reset
WhilePrintingRecords;
Redim strArry[1];
Redim numArry[1];
BooleanVar blnArrayDimmed := false;

-dave

 

[Halfcan]

Thanks Dave, I had to define the string and number variables first, but it works!


//@Reset
StringVar Array strArry;
NumberVar Array numArry;
WhilePrintingRecords;
Redim strArry[1];
Redim numArry[1];
BooleanVar blnArrayDimmed := false;

I'd like to learn more about working with arrays and variables. Do you have any ideas for good sources to start learning about the basics?
Thanks again, another star very much earned.

HC

 

[Halfcan]

Hi Vidru.

I have a problem.

If I have too many strings that are different, the number of characters resulting from @DisplayResults is over 254.

The formula @DisplayResults dies at 254 characters.

Any Ideas?

Thanks,
hc

 

[Halfcan]

Damn. I'm using CR 8.5. - which has a 254 limit for output from a formula.
hc

 

[vidru]

Fret not, young Halfcan... no need to rush out an buy that upgrade just yet (although that sure would be easier than what follows).

You'll have to create multiple @Display formulas to account for that potential. Once you determine about how many items you can safely fit and display at once, you can set up set up each formula to only deal with a specific number of array elements.

For instance, say the number of items you can safely use in the Display formula is 10, you would rewrite your formulas like this:

//@DisplayFirst10
WhilePrintingRecords;
StringVar Array strArry;
NumberVar Array numArry;
NumberVar Upper;
NumberVar i;
StringVar Array Output;

if count(strArry) >= 10 then
  Upper := 10
else
  Upper := Count(strArry);

Redim Output[Upper];
for i := 1 to Upper do(
  output[i] := strArry[i] + " occurred " + ToText(numArry[i],0) + " time(s).";);

Join(Output, chr(13));

... and ...

//@DisplaySecond10
WhilePrintingRecords;
StringVar Array strArry;
NumberVar Array numArry;
NumberVar Upper;
NumberVar i;
StringVar Array Output;
NumberVar Elements;

if count(strArry) > 10 then(
  if count(strArry) <= 20 then(
    Upper := Count(strArry);
    Elements := Count(strArry) - 10;)
  else(
    Upper := 20;
    Elements := 20;))
else
  Upper := 0;

if Upper <> 0 then(
  Redim Output[Elements];
  for i := 1 to Elements do(
    output[i] := strArry[i + 10] + " occurred " + ToText(numArry[i + 10],0) + " time(s).";);

  Join(Output, chr(13));)
else
  "";

You can keep extending these formulas as high as you need to go. The easiest way to display them all would be to put all of the Display formulas into a single text object.

-dave

 

[Halfcan]

Hi Dave, Thanks alot.
I've been testing this and I have a few questions.

When I go further on extending these forumlas ie: 20-30, 30-40 and so on, I need to repeat the formulas in the same order each time, right?
Example: formula 1, formula 2, formula 1, formula 2 etc...
(while just changing the numbers in both)

Second question, I am getting an error on formula 2:
"A subscript must be between 1 and the size of the array."

I think this is coming from the Elements variable, but I don't fully understand how the formula works.

The data I'm using has 83 different strings, and I've created formulas 3 and 4 to cover string#'s 20-30 and 30-40 but it doesn't get that far. (Dies on formula 2.)

Thanks,

HC

 

[vidru]

When I go further on extending these forumlas ie: 20-30, 30-40 and so on, I need to repeat the formulas in the same order each time, right?

Right, so you'd end up with: @DisplayFirst10 (1-10), @DisplaySecond10 (11-20), @DisplayThird10 (21-30), @DisplayFourth10 (31-40), @DisplayFifth10(41-50), @DisplaySixth10 (51-60), @DisplaySeventh10 (61-70), @DisplayEighth10 (71-80), @DisplayNinth10 (81-90), etc.

I did find a problem with the Second10 formula, but I wasn't getting a subscript error (it just wasn't displaying what I thought it would because of how I was handling the Element variable). When I was testing, I got subscript errors a couple of times, and finally got rid of them after separating all of the "if...then's" with parentheses. You may need to check the placement of the parentheses within the nested If statements. At any rate, here is what works for me:

//@DisplaySecond10
WhilePrintingRecords;
StringVar Array strArry;
NumberVar Array numArry;
NumberVar Upper;
NumberVar i;
StringVar Array Output;
NumberVar Elements;

if count(strArry) > 10 then(
  if count(strArry) <= 20 then(
    Upper := Count(strArry);
    Elements := Count(strArry) - 10;)
  else(
    Upper := 20;
    Elements := 10;))
else
  Upper := 0;

if Upper <> 0 then(
  Redim Output[Elements];
  for i := 1 to Elements do(
    output[i] := strArry[i + 10] + " occurred " + ToText(numArry[i + 10],0) + " time(s).";);

  Join(Output, chr(13));)
else
  "";

And here's what the @DisplayThird10 formula should look like, with the values that need to change for each additional formula denoted in red (you'll notice that this is identical to the @DisplaySecond10 formula except where noted - those are the values that you should increase by 10 for each additional formula):

//@DisplayThird10
WhilePrintingRecords;
StringVar Array strArry;
NumberVar Array numArry;
NumberVar Upper;
NumberVar i;
StringVar Array Output;
NumberVar Elements;

if count(strArry) > [COLOR=red]20[/color] then(
  if count(strArry) <= [COLOR=red]30[/color] then(
    Upper := Count(strArry);
    Elements := Count(strArry) - [COLOR=red]20[/color];)
  else(
    Upper := [COLOR=red]30[/color];
    Elements := 10;))
else
  Upper := 0;

if Upper <> 0 then(
  Redim Output[Elements];
  for i := 1 to Elements do(
    output[i] := strArry[i + [COLOR=red]20[/color]] + " occurred " + ToText(numArry[i + [COLOR=red]20[/color]],0) + " time(s).";);

  Join(Output, chr(13));)
else
  "";

Let me know how it goes.

-dave

 

[Halfcan]

I knew it had something to do with reseting "Elements"!

I was just trying to reset it somewhere after completing the first formula....(just stabbing in the dark.)

From //@DisplaySecond10:

<snip>

Upper := 20;
Elements := 10)
else
Upper := 0;

</snip>

Not sure if this was intentional, but the @DisplaySecond10 formula in the previous post is differnt than this last post. Is that what you meant by "handling the Elements" varible?
Anyway, I works!

So the Elements var is reset back to 10 at the start of each new formua. Awesome!

Thanks a ton!
hc

 

[MJRBIM]

Virdu -

The AddToArrays forumla is giving me the following error under CR-10.

An array's dimension must be an integer between 1 and 1000.

It appears to be on the line....

Redim Preserve strArry[UBound(strArry) + 1];

Any ideas around this...?

TIA for the help.

 

[vidru]

Hmmm... it works fine for me in CR 10. The original formula(s) should work for you with the exception of:

[tt]StringVar Array tmpArry := Split({Table.Field},";");[/tt]

... which you should change to:

[tt]StringVar Array tmpArry := Split({Table.Field}," ");[/tt]

... since your delimiter is a space, as opposed to halfcan's field, which was delimited by semicolons.

For future reference (for me, or anyone else reading this), [navy]MJRBIM[/navy]'s original thread was: thread767-1067340

-dave

 

[MJRBIM]

I had already updated the Split...and only get this error around page 8...

I'm guessing that we have more than 1000 different values in the Array.

Any ideas...?

 

[vidru]

Ahhhh yes, that could be a problem. You may have to create at least one more array. You can use UBound to get the element count of the first array. Once it hits 1000, the formulas will get more complicated, as you'll still have to compare the values to both arrays, but if the word doesn't already exist, assign the value to the second array.

Sorry I don't have time to flesh that out completely, but something like the above should work.

-dave