Scripting a sum column on a dataset with action performed

I’m trying to break my reliance on SQL to do everything and push myself to start scripting more and am having an issue. I have a dataset that is capturing 5 timed events every time a press runs, “Time Pressing”, “Time Empty”, “Time Prestanding”, “Time Closing”, “Time Open”. User has requested to have a button that they can press to change the dataset so that instead of seeing those values they just see a sum of all 5 columns. I have a row selector on the screen and a have a power table with the data attached to the data out attribute in the row selector. This gives them the ability to quickly parse out the data and they want to keep that setup, just have the ability to change the view in the power table to sum the rows, and if something seems way to high to then to view each time value in the dataset on click.

I’ve wrote the following to just print to the console and it kind of works, but it’s summing all the rows present in the dataset, and I want it to sum each row and return each rows value instead of the table’s value.

table=event.source.parent.getComponent('Power Table').data
data = system.dataset.toPyDataSet(table)
total = 0
for row in data:
	total += row[4]
	total += row[5]
	total += row[6]
	total += row[7]
	total += row[8]
print "Total", total

Once I get it to show each row’s value in the power table, I’m assuming I want to take out the print command and use the system.dataset.addColumn function to return this how I want it?

Thanks for any help you can provide to this thick head of mine.

You can do something like

dsData = system.dataset.toPyDataSet(event.source.parent.getComponent('Power Table').data)
rowttl = 0.0
newDS = []
hdr = ['Col1','col2','col3','ttl']
for row in dsData:
	for col in [1,2,3]:
		rowttl += row[col]
newTbl = event.source.parent.getComponent('Power Table 1') = system.dataset.toDataSet(hdr,newDS)

I have it writing to a second table just so you can see what it does.
You can adjust the headers, column names, etc. and then just put it back to Power Table on the second to last line to overwrite the original table.

1 Like

This is working awesome on the first row, but on the second row it’s adding in the values from the previous row ttl as well, each row is like a full cycle is it possible to have the tll value just be calculated each row instead of cumulative? Thank you so much for the help for sure

Consider using my view() expression function to help you. You can compose your idea in Pseudo-SQL, then look at the python its debug mode displays for its solution to your problem. That python can be put in its own script module function and further optimized if you feel the need.

1 Like

Put the rowttl = 0.0 above the for col line and it will reset each time.

1 Like

That got it thank you my man!