Script to Sum Multiple Table Columns

Ignition
1

Long time reader, first time caller.

I am looking to create a script that sums all the values in the "quantity" column for 10 datasets.
The number of rows may change based on how many different products we create that hour.

Hour Values are datasets, column 1 are strings, column 2 are quantities.

columnname = 'COUNT(WO)'
print columnname
df = system.tag.readBlocking('[default]353/KPI/First Shift/Hour 1 Tank Counts')[0].value
Hour2 = system.tag.readBlocking('[default]353/KPI/First Shift/Hour 2 Tank Counts')[0].value
Hour3 = system.tag.readBlocking('[default]353/KPI/First Shift/Hour 3 Tank Counts')[0].value
Hour4 = system.tag.readBlocking('[default]353/KPI/First Shift/Hour 4 Tank Counts')[0].value
Hour5 = system.tag.readBlocking('[default]353/KPI/First Shift/Hour 5 Tank Counts')[0].value
print df

total =system.math.sum(Hour3.getColumnIndex('COUNT(WO)'))

2

What do you want to do with the result? And how often do you want to compute this?

1 Like
3

Additionally, does it have to be a script? Expressions make this task pretty easy if your list of input dataset tags is fixed (and with one of Phil's modules, technically even that restriction can be lifted).

4

As mentioned above, there are different ways to go about this, although I went ahead and cleaned your script up a little for example.

columnName = 'COUNT(WO)'
print columnName
tagPaths = ['[default]353/KPI/First Shift/Hour %s Tank Counts' % i for i in xrange(1,6)]
# returns qualified values
qValues = system.tag.readBlocking(tagPaths)
total = 0

for qVal in qValues:
	ds = qVal.value
	l = ds.getColumnAsList(ds.getColumnIndex(columnName))
    # if the list has null values, this won't work
	colSum = system.math.sum(l)
	print colSum
	total += colSum

print total
# without looping
df = qValues[0].value
Hour2 = qValues[1].value
Hour3 = qValues[2].value
Hour4 = qValues[3].value
Hour5 = qValues[4].value
print df
5

This is going to be set up in a GateWay event. I am putting it in a timer scrip to be called every 10seconds. Traditionally I would do this on the SQL side, but I do not have access to pull from this table and need to use the data I already have to quantify total.
I will be taking the end sum and doing a system.tag.writeblocking(Dest,Value) in the end.

Python is new to me...

6

Expression is probably the way to go here.

7

Can you show me an example of an expression that would sum dynamic rows in a table?

8

The expression is trivial:

sum(
	sum({[default]353/KPI/First Shift/Hour 1 Tank Counts}, "Count(WO)"),
	sum({[default]353/KPI/First Shift/Hour 2 Tank Counts}, "Count(WO)"),
	sum({[default]353/KPI/First Shift/Hour 3 Tank Counts}, "Count(WO)"),
	sum({[default]353/KPI/First Shift/Hour 4 Tank Counts}, "Count(WO)"),
	sum({[default]353/KPI/First Shift/Hour 5 Tank Counts}, "Count(WO)")
)

The expression language's sum has builtin capability to extract a particular column from a dataset - sum - Ignition User Manual 8.1 - Ignition Documentation

You could put this in an additional expression tag, running on a 10 second rate.

I also started writing out a script to do it as well, though I haven't tested this:

columnname = 'COUNT(WO)'
basePath = '[default]353/KPI/First Shift/Hour {} Tank Counts' 
values = [qv.value for qv in system.tag.readBlocking([basePath.format(i + 1) for i in xrange(5)])]

return sum(value for value in column for column in ds.getColumnAsList(ds.getColumnIndex(columnname)) for ds in values)
5 Likes
9 
sum(sum(ds.getColumnAsList(ds.getColumnIndex(columnname))) for ds in values)
5 Likes
10

Worth pointing out that the expression can be dramatically easier on the eyes if you place the expression tag in the same folder as the counts, like so:

sum(
	sum({[.]Hour 1 Tank Counts}, "Count(WO)"),
	sum({[.]Hour 2 Tank Counts}, "Count(WO)"),
	sum({[.]Hour 3 Tank Counts}, "Count(WO)"),
	sum({[.]Hour 4 Tank Counts}, "Count(WO)"),
	sum({[.]Hour 5 Tank Counts}, "Count(WO)")
)

Which then also makes it possible to place a copy of the tag into the Second Shift folder, and have it work without changes.

6 Likes
11

Thank you! I appreciate the quick feedback! Works great