Crystal Reports: Arrays and Filter

CR 8.5

i am reading an example of using filter on an array:
filter(["abc abc abc abc abc","hjjh","hj abc hj"],"abc")[1]

what does the [1] mean at the end?


this is what i'm trying to do:
shared stringVar array qm3Array1;
shared stringVar array qm9Array1;
local stringVar qm3t1;
local stringVar qm9t1;
local numberVar qm3sizeArray1:=ubound(qm3Array1);
local numberVar qm9sizeArray1:=ubound(qm9Array1);
local numberVar cnt;
if cnt=0 then cnt:=1;
local stringVar result;

if qm3sizeArray1>1 then
(
    qm9t1:=split(qm9Array1[cnt],",")[1];
    //qm3t1:=split(qm3Array1[cnt],",")[1];
    cnt:=cnt+1;
    result:=filter(qm3Array1,qm9t1)[1];
);
result;

if i remove the [1] from the end of the filter statement, i get an error about needing a subscription on the array.  if i replace [1] with [2] i get the same result as i have for [1].
i am trying to use filter to find/match the string in qm9t1 with an element in the array qm3Array1

so, for example, qm3Array1:
qm3Array1[1]=hammer,4
qm3Array1[2]=nail,5

and qm9t1=nail

i want to return 5.
LVL 44
zephyr_hex (Megan)DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MIKESoftware Solutions ConsultantCommented:
The "[1]" is identifying the FIRST item in the ARRAY.

As an example...IF you had a STRING like this:

   ABC, DEF,GHI

And if you used the SPLIT function to split it out you would receive the following:

This:
split(table1.StringField),",") [1]

Would return the FIRST item in the STRING:      ABC

This:
split(table1.StringField),",") [2]

Would return the SECOND item in the STRING:    DEF

and so on...

Hope it helps
MikeV

MIKESoftware Solutions ConsultantCommented:
You are getting the same result because the SECOND ITEM [2] in the array is the same as the FIRST [1]
Mike McCrackenSenior ConsultantCommented:
Is FILTER a function you wrote or is it in Crystal?

What is FILTER supposed to do?

mlmcc
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

zephyr_hex (Megan)DeveloperAuthor Commented:
filter is a function in crystal, and it is the function that i have a question about how the [1] works.  i understand how it works with split, but it seemed to be working differently with filter because i was getting the same result regardless of whether i used a [1] or [2] (unlike split...which would give me the first or second part of a string)

filter is supposed to filter an array and return results that match.  i'm not sure if there is another function that would accomplish what i'm trying to do.  basically, i have a string and i need to find the match in an array.

i've looked up the syntax of filter, and i'm not seeing an explanation of the [1] on the end, although the example i came across shows that [1] (and i get an error if i don't use it in my formula)

here is the site where i found that example:
http://www.inetsoftware.de/products/crystalclear/designer-manual/default.html?http://www.inetsoftware.de/products/crystalclear/designer-manual/formula/st-filter.html
zephyr_hex (Megan)DeveloperAuthor Commented:
CRXIuser2005  - the [2] item is not the same as the [1] item in my array (example element in my array:  hammer, 4).  if i use split, [1] returns the item name (hammer) and [2] returns the quantity (4).  filter is not doing that.  maybe because my delimiter is "," and not a space?  the syntax for split allows me to declare my delimiter, and i'm not seeing that option in the syntax for filter.  filter is returning hammer,4 regardless of whether i use [1] or [2] and i can't figure out how to identify the delimiter (i originally thought filter was just returning hammer, but it was because my field was too short... i stretched it out and i can see it is returning the entire element from the array).  maybe i need to use split in combo with filter?  but then why does filter require the array subscription at the end of it?  i get an error if i dont have that subscription.
frodomanCommented:
zephyr,

What the "filter" function does it to take out part of the string and leave a remaining string.  So by using filter you are taking characters out of the string.  The (1) gives you the first group of characters left after you filter out what you don't need.  The (2) gives you the second group of characters left after you filter out what you don't need.  

My question is what you're trying to accomplish - are you sure FILTER is the function you really want?

frodoman
zephyr_hex (Megan)DeveloperAuthor Commented:
frodoman-
i am not sure that filter is what i want

i am trying to do the following:

i have two arrays:
qm3Array1 and qm9Array1

both arrays hold elements of the following format:  hammer,4  (a part name followed by a quantity)
but the items in the two arrays are not necessarily in the same order, and it is possible that there are items in one array that are not in the other

i need to match the item in qm9Array1 with the same item in qm3Array1, and then subtract the quantity of the item in qm3Array1 from the quantity of the item in qm9Array1

for example:
qm9Array1[1]=nail,6
qm9Array1[2]=saw,3
qm9Array1[3]=screw,8

qm3Array1[1]=paint,6
qm3Array1[2]=screw,4
qm3Array1[3]=saw,2

so i want to iterate through the items in qm3Array1 and see if there is a match in qm9Array1.  if a match is found, then subtract the two quantities.

paint would not find a match, and return 0
screw would find a match, and return 4 (8-4)
saw would find a match, and return 1 (3-2)

if filter is not the right function, can you please suggest a better one.  thanks :)
frodomanCommented:
I think this must be a two step process.  Sounds like you want to use SPLIT on the first set to separate the items and quantities into an array and then you want to loop through the array and use INSTR to find the matching item in the second set.

frodoman
frodomanCommented:
Actually I don't think you'll need INSTR either.  Take a look at this formula - it should be about 99% of what you want if I understand correctly:

======================================
stringVar array qm9 := ['nail,6','saw,3','screw,8'];
stringVar array qm3 := ['paint,6','screw,4','saw,2'];
numberVar counter;
numberVar counter2;
stringVar target;
numberVar qty1;
numberVar qty2;
numberVar result;
stringVar all_results;

for counter := 1 to ubound(qm3) do
(
   target := split(qm3[counter],",")[1];
   qty1 := cdbl(split(qm3[counter],",")[2]);
   qty2 := -999; //default value
   for counter2 := 1 to ubound(qm9) do
   (
      if split(qm9[counter2],",")[1] = target then
         qty2 := cdbl(split(qm9[counter2],",")[2]);
   );

   if qty2 = -999 then
      result := 0
   else
      result := qty2 - qty1;

   all_results := all_results & " " & cstr(result);
);
all_results;
======================================

You can just copy and paste this formula into your report - it's self-contained so it should work regardless of your data.

Obviously it's using hard coded initial values instead of a field and returning all of the matches at once - not sure if you want that or want individual results reading through a record.  I should be able to help w/ any further modifications that you need.

frodoman

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
frodomanCommented:
Futher information: Depending on the size of your array, this might take a while to execute.  If it's unruly then it can be done more efficiently by joining the second array into a single long string and using the INTSR function - this is what I was originally thinking - it's more efficient on longer arrays but also more complicated.

frodoman
zephyr_hex (Megan)DeveloperAuthor Commented:
frodoman-
what you have makes sense...
qty1 := cdbl(split(qm3[counter],",")[2]);

what does cdbl do?
also: all_results := all_results & " " & cstr(result)
what does cstr do?


i initially tried using a single long string, but the issue i ran into was the limit of 254 characters for string size in CR 8.5.

the arrays in my test data are about 60 elements long.  the actual data will eventually be much, much larger (about 10,000 items or so)
if i run into issues with my array size, i may break the report down and run it on smaller groups of items (so there would be multiple reports)

i'll let you know if i run into issues using the method you've suggested.
frodomanCommented:
cdbl - converts text to number
cstr - converts number to text

Here's the potential issue with the size of your actual data - it's an exponential growth pattern.  You have one loop inside another loop so if there are 60 elements in each array then you have 60 passes through the inner loop for each pass through the outer loop.  Total of 60 x 60 = 3600.  When you have 10,000 in each array then you have 10000 x 10000 = 100,000,000  -- that's going to take a while to complete so smaller groups may make sense.  The technique is sound and it should work, it's just not going to be very responsive.  

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.