zephyr_hex (Megan)
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(qm3A rray1);
local numberVar qm9sizeArray1:=ubound(qm9A rray1);
local numberVar cnt;
if cnt=0 then cnt:=1;
local stringVar result;
if qm3sizeArray1>1 then
(
qm9t1:=split(qm9Array1[cnt ],",")[1];
//qm3t1:=split(qm3Array1[c nt],",")[1 ];
cnt:=cnt+1;
result:=filter(qm3Array1,q m9t1)[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.
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(qm3A
local numberVar qm9sizeArray1:=ubound(qm9A
local numberVar cnt;
if cnt=0 then cnt:=1;
local stringVar result;
if qm3sizeArray1>1 then
(
qm9t1:=split(qm9Array1[cnt
//qm3t1:=split(qm3Array1[c
cnt:=cnt+1;
result:=filter(qm3Array1,q
);
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.
You are getting the same result because the SECOND ITEM [2] in the array is the same as the FIRST [1]
Is FILTER a function you wrote or is it in Crystal?
What is FILTER supposed to do?
mlmcc
What is FILTER supposed to do?
mlmcc
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
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
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.
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
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
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 :)
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 :)
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
frodoman
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
frodoman
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.
what you have makes sense...
qty1 := cdbl(split(qm3[counter],",
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.
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.
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.
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),
Would return the FIRST item in the STRING: ABC
This:
split(table1.StringField),
Would return the SECOND item in the STRING: DEF
and so on...
Hope it helps
MikeV