Avatar of zephyr_hex (Megan)
zephyr_hex (Megan)
Flag for United States of America asked on

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.
Crystal Reports

Avatar of undefined
Last Comment
frodoman

8/22/2022 - Mon
Marcus Aurelius

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

Marcus Aurelius

You are getting the same result because the SECOND ITEM [2] in the array is the same as the FIRST [1]
Mike McCracken

Is FILTER a function you wrote or is it in Crystal?

What is FILTER supposed to do?

mlmcc
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
zephyr_hex (Megan)

ASKER
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)

ASKER
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.
frodoman

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
zephyr_hex (Megan)

ASKER
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 :)
frodoman

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
ASKER CERTIFIED SOLUTION
frodoman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
frodoman

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
zephyr_hex (Megan)

ASKER
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.
frodoman

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.