Sum/aggregate expression on a array of json data property?

Ignition
1

I have a table in perspective that’s data property is a array of json objects. We do this so we’re able to manipulate row background colors.

In this same table, I want to get a sum of a column, in this case, a boolean column named “Yes” with a check box to count how many were checked. I am using sum({this.props.data}, "Yes") but this is not calculating the correct value.

Do these aggregate functions only work on actual dataset type data properties? Is there a way around this or will I have to resort to some scripting on a table extension function?

2

According to the user manual, sum only accepts datasets, or an arbitrary amount of ints/floats as parameters to sum up, and not json data.

The script to solve your problem should be very simple, perhaps a custom property on your table with a binding onto props.data–something like this:

# given this is in, say, a property binding on a 
# custom property which references your table's props.data
sum = 0
for row in value: # 'value' is your table's props.data
    if row["Yes"] == True:
        sum += 1
return sum
1 Like
3

At some point in the future, we’re planning to have the aggregate expression functions work on json objects and arrays, but it’s a non-trivial amount of refactoring to make sure it doesn’t break other things.

2 Likes
4

Good to know. I tried doing a sum(toDataSet({this.props.data}),"Yes") as an expression, but realized the toDataSet just turned it into a dataset with a single column of qualified values.

5

Interested in the status of this improvement :slight_smile:

When looping over the filter results on a perspective table to sum a column is ugly.

for row in currentValue.value:
	addon += row.value['addon'].value
6 
sum(row['addon'] for row in currentValue.value)

Assuming that currentValue.value is an Array of Objects

7

Yeah, that is a cleaner way to write it for sure, but it's not an array of objects, I think it is an array of qualified values.

1 Like
8

Interesting....where is this script? Is it a property change?

The data prop on a perspective table (as far as I can tell) can only be a Dataset, an Array of Arrays, or an Array of Objects.

EDIT: Looks like in a property change event, it is, as you say converted into an Array of qualified values. I would not have expected that.

2 Likes
9

It's on the props.filter.results.data and yes it's a change event script.