Reporting Tag Historian Query Max daily value

Ignition
1

I am having some trouble getting the information I am looking for from a report.

I have a tag historian query providing a 10min time-weighted average from a group of tags. simplified version pictured below:

image

I would like to report the maximum value per day from the 10 min results. From reading other threads I think I might be required to create a query using a script. so far I have been able to return the maximum value from the entire dataset but I require the max of the 10min results each day, for any given date range.

Header = ['date','val']
		NewDataset = []
		
		rawDataset = data['DailyAverage10min'].getCoreResults()
		
		for row in range(rawDataset.rowCount):
			date = rawDataset.getValueAt(row,'t_stamp')
			val = max(rawDataset.getColumnAsList(rawDataset.getColumnIndex("val")))
			
			NewDataset.append([date, val])
			
		NewDataset = system.dataset.toDataSet(Header, NewDataset)
		
		data ['NewDailyAverage10min'] = NewDataset

any advice appreciated.

2

You could definitely handle this in a SQL query, however then you’ll need to handle unioning of the partition tables.

Using what you’ve already got, you could do something like…

dailyMaxValues = {}
for row in range(rawDataset.rowCount):
	date = system.date.format(rawDataset.getValueAt(row,'t_stamp'), 'yyyy-MM-dd')
	val = rawDataset.getValueAt(row,'val')
	
	dailyMaxValues[date] = max(dailyMaxValues.get(date), val)

ds_header = ['date', 'val']	
ds_data = []
for key in dailyMaxValues.keys():
	ds_data.append([key, dailyMaxValues[key]])

ds = system.dataset.toDataSet(ds_header, ds_data)

I’m not sure if system.date.format works with t_stamps stored as epoch dates…

Edit: I edited the script as I was overwriting the original data variable, as discussed in replies below

1 Like
3

i have given this a try but getting the following error:

WARN: Error invoking script.Traceback (most recent call last):
File “function:updateData”, line 16, in updateData
TypeError: list indices must be integers

	dailyMaxValues = {}
	rawDataset = data['DailyAverage10min'].getCoreResults()
	for row in range(rawDataset.rowCount):
		date = system.date.format(rawDataset.getValueAt(row,'t_stamp'), 'yyyy-MM-dd')
		val = rawDataset.getValueAt(row,'val')
		
		dailyMaxValues[date] = max(dailyMaxValues.get(date), val)
	
	header = ['date', 'val']	
	data = []
	for key in dailyMaxValues.keys():
		data.append([key, dailyMaxValues[key]])
	
	ds = system.dataset.toDataSet(header, data)
	data ['NewDailyAverage10min'] = data
4

I don’t have your whole code, what is line 16?

5

sorry that is the last line:

data ['NewDailyAverage10min'] = data
6

Ah, I overwrote that variable, data, and used it to store the data to create the new dataset. You’ll need to rename one of them.
Maybe edit it to this (i’ll fix it in my other post as well for posterity):

	ds_data = []
	for key in dailyMaxValues.keys():
		ds_data.append([key, dailyMaxValues[key]])
	ds = system.dataset.toDataSet(header, ds_data)
7

Amazing that seems to work, thanks for your help it!

1 Like
8

following on, is it possible to achieve the same with daily average? I hoped that

dailyMaxValues[date] = max(dailyMaxValues.get(date), val)

could have become:

dailyAveValues[date] = AVE(dailyAveValues.get(date), val)

but alas it is not that simple.

9

Python 2.7 doesn’t have an average function (that I know of). 3 does, but simply replacing ‘max’ with ‘mean’ would take the average each time of the last average value and the new value; it won’t get you the average of all of the daily values.

You need to do something like this (untested):

	dailyAvgValues = {}
	dailyAvgValCount = {}
	rawDataset = data['DailyAverage10min'].getCoreResults()
	for row in range(rawDataset.rowCount):
		date = system.date.format(rawDataset.getValueAt(row,'t_stamp'), 'yyyy-MM-dd')
		val = rawDataset.getValueAt(row,'val')
		
		dailyAvgValues[date] = dailyAvgValues.get(date) or 0 + val
		# keep track of how many values we've summed together
		dailyAvgValCount[date] = dailyAvgValCount.get(date) or 0 + 1
	
	for key in dailyAvgValues.keys():
		# divide the summated total daily values by the count of values summated, giving the average daily value
		dailyAvgValues[key] = dailyAvgValues[key]/dailyAvgValCount[key]
	
	header = ['date', 'val']	
	data = []
	for key in dailyAvgValues.keys():
		data.append([key, dailyAvgValues[key]])
	
	ds = system.dataset.toDataSet(header, data)
	data ['NewDailyAverage10min'] = data
10

The code compiles but the numbers don’t seem quite right.

I adjusted the time interval on the raw data set to make it easier to check the Ave result against the time interval raw. It looks like the average result is showing the first value of each date.

image

11

Try adding some print statements to check the value of the two dictionaries within the first for loop and see if the avgvalues dict is correctly adding the new val to the previous total for the right day. Probably print the date and val as well:

	dailyAvgValues = {}
	dailyAvgValCount = {}
	rawDataset = data['DailyAverage10min'].getCoreResults()
	for row in range(rawDataset.rowCount):
		date = system.date.format(rawDataset.getValueAt(row,'t_stamp'), 'yyyy-MM-dd')
		val = rawDataset.getValueAt(row,'val')
		
		dailyAvgValues[date] = dailyAvgValues.get(date) or 0 + val
		# keep track of how many values we've summed together
		dailyAvgValCount[date] = dailyAvgValCount.get(date) or 0 + 1

	print ",".join([date, val, dailyAvgValues[date], dailyAvgValCount[date]])
12

It doesn’t like the join statement.

WARN: Error invoking script.Traceback (most recent call last):
File “function:updateData”, line 13, in updateData
TypeError: sequence item 1: expected string or Unicode, float found

Where can you view the print statements? I tried to use the Script Console but but as this is using datasets from reports, I couldn’t get it to work.

13

Yep my bad, I relied on implicit order of operations and got it wrong…
See the two EDIT comments for changes. Also, I created a dummy dataset at the start

dailyAvgValues = {}
dailyAvgValCount = {}
rawDataset = [[system.date.parse('2020-06-20 00:00:00'), 1]]
rawDataset.append([system.date.parse('2020-06-20 00:5:00'), 3])
rawDataset.append([system.date.parse('2020-06-21 00:00:00'), 4])
rawDataset.append([system.date.parse('2020-06-21 00:04:00'), 3])

rawDataset = system.dataset.toDataSet(['t_stamp', 'val'],rawDataset)

for row in range(rawDataset.rowCount):
	date = system.date.format(rawDataset.getValueAt(row,'t_stamp'), 'yyyy-MM-dd')
	val = rawDataset.getValueAt(row,'val')
	
	dailyAvgValues[date] = (dailyAvgValues.get(date) or 0) + val ## EDIT: ADDED BRACKETS
	
	# keep track of how many values we've summed together
	dailyAvgValCount[date] = (dailyAvgValCount.get(date) or 0) + 1.0 ## EDIT: ADDED BRACKETS AND CHANGED FROM 1 TO 1.0 so that decimal averages are shown if values are integers, otherwise results are floored
for key in dailyAvgValues.keys():
	# divide the summated total daily values by the count of values summated, giving the average daily value
	print dailyAvgValues[key],dailyAvgValCount[key],dailyAvgValues[key]/dailyAvgValCount[key]
	dailyAvgValues[key] = dailyAvgValues[key]/dailyAvgValCount[key]

header = ['date', 'val']	
data = []
for key in dailyAvgValues.keys():
	data.append([key, dailyAvgValues[key]])

ds = system.dataset.toDataSet(header, data)
print data
2 Likes