Basic Math With Arrays

[Zugdud]

Howdy folks, I dont use crystal reports a whole lot so take it easy on me! I am trying to figure out a way to come up with a formula that multiplies 2 fields together, one of the fields is an array of 5 integers (seperated by comma)

The only piece of the array that I need is the first number. For example heres a row of sample output:

Field A (dec) Field B (array on ints)
172.24 40,0,0,0,0


Field B is suppose to be a percentage but is stored in the DB as an int so I also need to convert it to a percentage before multiplication. The answer I am after would be:

68.896

 

[synapsevampire]

The second field isn't an array, it's a string which is being used to contain a comma deleimited array.

However you didn't state anything technical such as the version of your software nor the database used, which should accompany any technical post.

So I'll assume that what you're asking is:

Multiply one field by the 1st value within another field which contains a string field which needs to be parsed out.

You've 2 methods available:

{table.field1}*(val(left({table.field2},instr({table.field2,",")-1))*.01)

or you can use an array (Crystal version dependent):

whileprintingrecords;
{table.field1}*(val(split({table.field2},",")[1])*.01)

Should get you there.

-k

 

[Zugdud]

Thanks for the quick reply, sorry for the lack of info. I am using a Progress 9.1D Database and Fetching the info using Crystal Reports 8.5

I have the query correctly written, just trying to format the data a bit better. I Will give your formula a shot, thanks again!

 

[Zugdud]

just to follow up, I tried the formula but got an error when I try to run it with a report "string length is less then 0 or not an integer "here is the forumla.

{PriceLstParts.BasePrice}*(val(left({PriceLstParts.DiscountPercents},instr({PriceLstParts.DiscountPercents},",")-1))*.01)

I didnt do the best job explaining the second field (where I think the error is coming from), I looked through the data dictionary and here is the descritipon for said field:

Fmt: > 9.99 , Decimals 2, Extent 5, type decimal

Synop: An array that contains the discount percentage that is to be given for a corresponding price brake quantity

 

[Zugdud]

Hmmm or mabey you are right, crystal reports has the format of the value set to a string

 

[lbass]

I think the error means that you have some fields that do not have a comma. Try the following:

if isnull({table.fieldB}) then 0 else
if instr({table.fieldB},",") <> 0 then
(val(left({table.fieldB},instr({table.fieldB},",")-1))*.01){table.fieldA} else
(val({table.fieldB})*.01)*{table.fieldA}

I also added in a null check, just in case you run into this.

-LB

 

[Zugdud]

Thanks for the follow up, that seems to work except for one slight problem. I need to have my eyes checked because the numbers are not seperated by comma, they are seperated by semi colen! The output says 40;0;0;0;0 rather then 40,0,0,0,0. I attempted editing the code you sent but it appears the semi colen is some kind of special character? Hmmm

 

[bdreed35]

Alter lbass's formula like this:

if isnull({table.fieldB}) then 0 else
if instr({table.fieldB},";") <> 0 then
(val(left({table.fieldB},instr({table.fieldB},";")-1))*.01){table.fieldA} else
(val({table.fieldB})*.01)*{table.fieldA}


~Brian

 

[Zugdud]

That was it! Thankyou all very much!