WKetcham’s response gave me enough clues to make something work. For anyone trying to do the same (and there are quite a few posts on kWh readings) here’s my way in my first dabble into scripting.
[li]Add a table component. This one will, eventually, be hidden from view. It will do the grunt work in retrieving the records from the historian. [/li]
[li]Add a second table will be the manipulated data and will be filled by a Python script.[/li]
[li]Add a DateRange tool and we’ll add a script to it to extend the end-date by a few seconds (a minute is fine for my application) to include one more reading after the range of interest. i.e., If I want to calculate kWh used each hour then I need to retrieve the record for 00:00 the following day so I can subtract the 23:00 reading from that to calculate the final hour’s usage.[/li]
[li]Finally, any time the data in Table1 changes then run the script to calculate the hourly usage. Feed the output to Table2.[/li][/ol]
1 & 2. Create the tables
[ul]The one on the left, “kWh readings” is bound to the Tag History for the kWh of interest. Binding shown below.[/ul]
[ul]Right-click the left table and add a custom property.[/ul]
[ul]The “Diff table” on the right is blank for now.[/ul]
3. The DateRange component
We need to do a trick with the date selector. Since I want the energy used per hour I need one extra reading to be returned from the historian - in this case the reading at 00:00 on the following day. We can do this by script. Right-click on the DateRange component and add the script.
For calculation of differences between readings we need one extra reading.
e.g. For hourly readings from 00:00 to 23:59:59 we need to add a few seconds
to the end time to make sure we get the reading for the end of the last period
at 00:00 the next day.
if event.propertyName == “event.source.endDate”:
event.source.endDate = event.source.endDate + datetime.timedelta(minutes=1)
At this stage your left table should work and select the records for the period of interest including the one following record.
4. Calculate the row differences
Now Right-click on the left table and add the following script:
[code]# Difference between dataset rows.
This script takes the dataset from a table component and generates an array
giving the timestamp and difference between rows.
if event.propertyName == “TableCustProp”:
# Pull the dataset property from a table component
data = event.source.parent.getComponent(“kWh readings”).data
# Create an array for the output data.
# Loop through the dataset, perform the diff calculation and add to the array.
for row in range(data.rowCount - 1):
oneRow = [data.getValueAt(row, 0), data.getValueAt(row + 1, 1) - data.getValueAt(row, 1)]
# Create headers for the new table.
headers = [“Timestamp”, “kWh”]
# Create the new dataset from the array.
dataOut = system.dataset.toDataSet(headers, rowsOut)
# Use our new dataset to fill in a Table
table = event.source.parent.getComponent(“DiffTable”)
table.data = dataOut
How it works
[ul]Changing the date range causes an update on the left table.[/ul]
[ul]This triggers the script which runs the calculation.[/ul]
I have a little work to do to figure out if the end-date modification works on start-up. I may have to force that separately.
Thanks for your interest and prompts.