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.
Count of populated columns
Moderators: chulett, rschirm, roy
Count of populated columns
mark_e
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)
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
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
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)+...)
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,''))
If your file format allows it, I would load t
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Stage variables approach isn't flexible enough to handle an arbitrary number of columns.dls wrote:What's wrong with using stage variables?
In your case, it seems appropriate.
Otherwise it's viable.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Hi,
you can also try doing this
regards
kumar
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))
kumar