Counting data within an array

[reporter42]

I'm using Crystal 10 against a Progress database via an ODBC connection.

I have a field in my database that is an array. It contains rates that correspond to meters. Each record can have up to 8 meters on it (also an array field) and therefore 8 rates (1 corresponding to each meter). I need to get a count of the number of meters on each rate. I'm thinking I need to do something with variable arrays but not quite sure how to start.

Data example:

Below is an example of the data that is in the rate field when just the field is displayed on the report.
record 1, rate field has 2;0;0;0;0;0;0;0
record 2, rate field has 2;3;0;0;0;0;0;0
record 3, rate field has 7;7;0;0;0;0;0;0
record 4, rate field has 6;7;0;0;0;0;0;0
record 5, rate field has 3;0;0;0;0;0;0;0

What I would like to see is just a count of the rates so
2 should be count of 2
3 should be count of 2
6 should be count of 1
7 should be count of 3

I would like the count to be in rate number order as displayed above; however would be willing to get what I get if I can get the correct counts. I know that I can split the array out into individual elements but I cannot figure out how to get it to count correctly.

 

[synapsevampire]

Horrible way to store data, I'd make additional fingers and toes out of your dba's spine to allow she/he to manually count them for you themselves.

Also, your data isn't stored as an aray, it's stored as a string which can be parsed out into an array.

To accomplish this, I'd create an array of however many types of rates you might have, let's say 10 for current demonstration purposes.

Set the AllRates array in the report header using:

whileprintingrecords;
numbervar array AllRates[10];

In the details use:

whileprintingrecords;
numbervar array AllRates;
numbervar array CurrentRates:=split({table.array},";");
numbervar counter;
For Counter := 1 to 8 do(
AllRates[CurrentRates[Counter]]:=AllRates[CurrentRates[Counter]]+1
);
1

Now you're counting the values into the container AllRates array.

To display it use:

whileprintingrecords;
numbervar array AllRates;
AllRates[1]

Increment the 1 for each value you wish to display.

Couldn't test, but this is very close.

-k

 

[reporter42]

I got the formulas to work with some minor modifications, (thanks for that - learned a lot just from that) now I'm trying to keep a count of the number of times a value is used.

So using same data as above
record 1, rate field has 2;0;0;0;0;0;0;0
record 2, rate field has 2;3;0;0;0;0;0;0
record 3, rate field has 7;7;0;0;0;0;0;0
record 4, rate field has 6;7;0;0;0;0;0;0
record 5, rate field has 3;0;0;0;0;0;0;0

on evaluation of record 1 allrates array has (2).
on evaluation of record 2 allrates array has (2,3).
on evaluation of record 3 allrates array has (2,3,7).
on evaluation of record 4 allrates array has (2,3,7,6).
on evaluation of record 5 allrates array has (2,3,7,6). no new value added as 3 is already in the array and I modofied the evaluation formula to only add if value doesn't exist in array.

On each record I need to create a running total to keep track of the number of times the value in the array is used in the report.

I have the following formula to do this but it's not acting as a running total.

formula - Count
numbervar array allrates;
numbervar array ratetotalcnt1;
stringvar array currentrates;
numbervar b := 1;
redim ratetotalcnt1[30];

while b <= ubound(allrates) do(

ratetotalcnt1 := if allrates <> 0 and
allrates = tonumber(currentrates[1])
then ratetotalcnt1 + 1
else ratetotalcnt1;

ratetotalcnt1 := if allrates <> 0 and
allrates = tonumber(currentrates[2])
then ratetotalcnt1 + 1
else ratetotalcnt1;
b := b + 1;
);
ratetotalcnt1[1]

when displayed I would like ratetotal1[1] (as an example) to show for each record (count formula is in the detail section)
record 1 should show 1,
record 2 should show 2,
record 3 should show 2,
record 4 should show 2,
record 5 should show 2,
for a final display in the report footer of 2.

However it shows
record 1 shows 1
record 2 shows 1
record 3 shows 0
record 4 shows 0
record 5 shows 0

The formula below works:

whileprintingrecords;
numbervar array allrates;
numbervar ratecount1;
stringvar array currentrates;
numbervar ratecount11;


ratecount1 := if allrates[1] <> 0 and
allrates[1] = tonumber(currentrates[1])
then ratecount1 + 1
else ratecount1;
ratecount1 := if allrates[1] <> 0 and
allrates[1] = tonumber(currentrates[2])
then ratecount1 + 1
else ratecount1;
ratecount1 := if allrates[1] <> 0 and
allrates[1] = tonumber(currentrates[3])
then ratecount1 + 1
else ratecount1;
ratecount1 := if allrates[1] <> 0 and
allrates[1] = tonumber(currentrates[4])
then ratecount1 + 1
else ratecount1;
ratecount1 := if allrates[1] <> 0 and
allrates[1] = tonumber(currentrates[5])
then ratecount1 + 1
else ratecount1;
ratecount1 := if allrates[1] <> 0 and
allrates[1] = tonumber(currentrates[6])
then ratecount1 + 1
else ratecount1;
ratecount1 := if allrates[1] <> 0 and
allrates[1] = tonumber(currentrates[7])
then ratecount1 + 1
else ratecount1;
ratecount1 := if allrates[1] <> 0 and
allrates[1] = tonumber(currentrates[8])
then ratecount1 + 1
else ratecount1;

But if I have to do this I'd have to create 30 formulas to accumulate up to 30 rates. I would rather if possible store the count in an array and then just have to do the 30 formulas to display the count.

I'm not very familiar with the while do or to for ... do functions so I probably have something wrong with that.


Also, and maybe the answer to this question may make the formulas easier, is there a way to see how many times a value is used in an array? Example, when creating the array currentrates from synapsevampire, a value may be repeated. example (2,2,2,0,0,0,0) Is there a way to see that the number 2 occurs 3 times? If so, that may make counting the number of times the number 2 occurs easier.

Thanks.