I am wanting to Sum a column in a dataset and get a percentage for each row and add that percentage to a new column.
You basically need to use the “Window” aggregate functions of your database, as described starting here for PostgreSQL. If you are using a database that’s too lame to support such functions, you’ll probably have to post-process your dataset with a jython script. Or do weird (and slow) joins between your current query and a grouped version of that data.
the dataset is on a client window and the script is in python.
this works but I am trying to get it to update the percentage in each row in that same column on an edit. It only updates the current row.
“if colIndex == 1:
#Percent of Total Column
sum = 0.0
for row in range(self.data.getRowCount()):
sum = sum + self.data.getValueAt(row, 1)
print 'sum = ', sum
percent = 0.123
percent = (newValue/sum)*100
print 'newValue = ', newValue, 'percent = ', percent
self.data = system.dataset.setValue(self.data, rowIndex, 2, percent)
self.data = system.dataset.sort(self.data, 1, 0)”
First, don’t write back to the same property. Use a custom property to bring in the data, and process that into a new dataset to assign to ‘data’.
Second, use a temporary variable to hold your dataset changes in progress. Only assign to ‘data’ at the end of the script. You are only getting the first row changed because that assignment is recursing into another execution of your script.
Finally, please always post code samples with triple-backquotes – ` – on the line before and on the line after, so it’ll be formatted for others to read clearly.
1 Like