The reason I am asking is because I have a Totalizer which I want to use Range Aggregation to get the day total. The problem is , there are zeros in the data which cause the return value to be incorrect.
I want to create a custom aggregation function to remove 0's from the return.
Here is an example of of an aggregate that I use to calculate the duration based on particular values of an integer tag.
def customStatusDuration(qval,interpolated,finished,blockContext,queryContext):
lastValue = queryContext.get('lastValue',False)
startTime = blockContext.get('startTime',None)
duration = blockContext.get('duration',0)
if all([qval.value == 0,not lastValue,qval.quality.good]):
startTime = qval.timestamp
elif all([startTime,qval.value,qval.quality.good,not finished]):
duration += system.date.secondsBetween(startTime,qval.timestamp)
#clear the start time so that we don't add time in between stops to the
#duration.
startTime = None
queryContext['lastValue'] = qval.value == 0
if finished:
if startTime is not None:
duration += system.date.secondsBetween(startTime,system.date.fromMillis(blockContext.blockEnd))
return duration
blockContext['duration'] = duration
blockContext['startTime'] = startTime
That depends on if the value needs to be persisted across multiple blocks.
For instance, in my example I persist the lastValue across the query, so that the next block knows what the last value of the previous block was, but I don't for the duration or startTime.
You can't not return from a python function - even if you don't return explicitly, it returns None.
Well, you could not return, by raising an error, but I doubt that helps here.
What exactly do you want your aggregation function to do ?
Or maybe a better question would be: What do you want queryHistory to return ?
But what are the other values ? The exact data points ? You were talking about range and a totalizer, so there must be more going on than just removing zeros ?
These would be flow totals, I want to calculate the Flow Total for a specified time range.
Where the Flow total is Latest Flow Total - Earliest Flow Total=Total Flow for that period of time.
The issue is , with large amounts of data I would have to run another loop after retrieving the data from the tag history query to remove the 0's from the dataset. I am hoping to use the custom aggregation function to do this when the data is being retrieved.
I can't see a way to skip a block in the aggregation function, except maybe if you were storing valid block data in the queryContext and returning that at the end, but that feels a bit... weird, and the return of queryHistory would have to be re-processed to get what you really need.
The period of time can be as long as a year , meaning if I can avoid looping through the data multiple times if would be nice.
The quick fix I have implemented, is to check if the value at index -1 and 0 ,if the value at the end of the array is equal to 0, I walk backwards (forwards for index 0) till I find a non-zero value .
Total=data[-1][1] - data[0][1]
Total = 5 - 1
Total = 4
Which is wrong.
Using this method I described, this is what I am doing.
data=[
["01-01-2023":0],<--check if data[0][1] is 0 , if so move to the next index
["02-01-2023":2],
["03-01-2023":3],
["04-01-2023":0],
["04-01-2023":4],
["05-01-2023":5] <--check if data[-1][1] is 0 ,if not use this as end value
]
data=[
["01-01-2023":0],
["02-01-2023":2],<--check if data[0][1] is 0 , if not use this as end value
["03-01-2023":3],
["04-01-2023":0],
["04-01-2023":4],
["05-01-2023":5] <--check if data[-1][1] is 0 ,if not use this as end value
]
Total=endValue - startValue
Total = 5 - 2
Total = 3
If the data was ""cleaned" and I expect no 0 values at the start or end of the array (assume there are totals for the start and end range) , I don't have to perform an extra step to do this check.
Unfortunately for some reason the totalizer tag has store random 0's in the database.
How do you know if your last value in the query when using custom aggregation?
Is there a array length value and index position provided in the queryContext?
Sounds to me like you shouldn't be using the tag historian, but transaction groups. Then use SQL lead() or lag() outside a subquery that discards the zeros.