Count of populated columns

[Mark_E]

Hi,
I have a question which I hope someone will be able to help me with.

I have a series of records which consist of payments over a given
period (11). Therfore there are 11 fields, some will be populated with 0 (i.e. no purchase) and other columns would be populated with a value eg 10.00(showing a purchase). What I need to do is to count all the columns in each >record where the purchase value is greater than 0 and then use this number to divide the sum of all the columns)

an example of the format is below

10, 0, 14.5, 0, 12.5, 0, 118.91
0, 0, 0, 11.8, 0, 55, 837.4
0, 0, 0, 5, 0, 0, 0


in the first example I would devide (155.91) by 4, the second example (904) by 3 and the final example (5) by 1. I am experimenting with setting up a staging variable to use as a counter in order to try and achieve my result but would like to know if anyone could recomend a simpler and cleaner way to achieve this.

[ArndW]

In order to do "array" type processing you could load your sequential as just one column. That gives a longish string, but you can parse that easily with the FIELD() function.

In terms of execution performance, I think that the (ugly)

Code: Select all

(In.Column1+In.Column2+...)/((IF In.Column1#0)THEN 1 ELSE 0)+(IF InColumn2#0 THEN 1 ELSE 0)+...)

would be quickest. But if you do have a string in the form:

In.WorkRecord = ",,,0,10,16.8,,,23.4,,"

then you could do a

Code: Select all

SUM(CONVERT(',',@FM,In.WorkRecord))/SUM(ORS(CONVERT(',',@FM,In.WorkRecord,''))

The advantage of the second is that it will work for any number of input numbers and do a correct average on nonzero elements.






If your file format allows it, I would load t

[dls]

What's wrong with using stage variables?

In your case, it seems appropriate.

[Mark_E]

Thanks guys for your advice, I achieved this through the ugly way

[ray.wurlod]

What's wrong with using stage variables?

In your case, it seems appropriate.

Stage variables approach isn't flexible enough to handle an arbitrary number of columns.

Otherwise it's viable.

[kumar_s]

Hi,
you can also try doing this

Code: Select all

count = Not(Not(field1)) + Not(Not(field2)) + Not(Not(field3)) + Not(Not(field3)) + Not(Not(field5))

regards
kumar