Hi All,
I have a range of data coming from energy meter and i can calculate consumption for a given time by max - min but once energy meter counter resets to zero and start again from zero, calculation gives wrong answer. is there any way to tackle this in ignition by script or anyway in SQL to split data if it goes to zero. below is an example:
Query: select max(k10)-min(k10) from PowerMeterSeries where K10>0
and answer
actually answer should be 1008
and table actually looks like
how should it to 1008?
ah i see
999+109-100 =1008
why would you substract 100 tho?
anyways
instead of saving the current k10, you could store the difference between values of every read i guess
or make an extra column for the difference if you need the other one too
100
10
140
749
0
0
15
5
23
56
10
the consumpion then would be the sum of the data
sum=1108
(guess you still have to substract the first value tho i think the consumtion should be 1108)
sum-100 = 1008
Hi @victordcq, thanks for your reply. I am quite new in ignition do you have any idea how I can subtract current value from last value in either SQL or Script.
which db do you have? sql server, mysql…?
Using scripting:
# Create a sample dataset
sampleHeaders = ['K10', 't_stamp']
sampleData = [
[100 , '2021-11-01 00:00:00'],
[110 , '2021-11-01 01:00:00'],
[250 , '2021-11-01 02:00:00'],
[999 , '2021-11-01 03:00:00'],
[0 , '2021-11-01 04:00:00'],
[None, '2021-11-01 05:00:00'],
[15 , '2021-11-01 06:00:00'],
[20 , '2021-11-01 07:00:00'],
[43 , '2021-11-01 08:00:00'],
[99 , '2021-11-01 09:00:00'],
[109 , '2021-11-01 10:00:00']
]
sampleDataSet = system.dataset.toDataSet(sampleHeaders, sampleData)
# ------------------------------------------------------------------
# Simulate query results.
dataIn = system.dataset.toPyDataSet(sampleDataSet)
# Initialize the variables.
consumption = 0
# Iterate ofer the query results. Zip lets us iterate over multiple sequences at once.
# The slice of dataIn[1:] starts at row 1 instead of row 0.
for row , nextRow in zip(dataIn, dataIn[1:]):
# Check for null value, keep older valid value
if row['K10'] is not None:
oldValue = row['K10']
# Check for null value, perform calculations if valid number
if nextRow['K10'] is not None:
newValue = nextRow['K10']
# If new value is greater, add difference
if newValue >= oldValue:
consumption += newValue - oldValue
# Otherwise, we can assume the new value has rolled over.
# Also assume largest valid value is 999.
else:
consumption += (999 - oldValue + newValue)
print '{:3d} : {}'.format(newValue, consumption)
Output:
110 : 10
250 : 150
999 : 899
0 : 899
15 : 914
20 : 919
43 : 942
99 : 998
109 : 1008
using some sample data in sql (change x to k10 and id to timestamp i guess)
select sum(diff) from (
SELECT
IIF(ISNULL(x,0)-lag(ISNULL(x,0),1,0) over (order by id) >=0, ISNULL(x,0) - lag(ISNULL(x,0),1,0) over (order by id), 0) as diff
FROM [test].[dbo].[Table_1]
) t
ah i forgot to do minus the first xd
@JordanCClark answer is proabbly easier anyways xd
You can do a running total well enough in SQL, but it gets trickier when the raw value rolls over.
I do exactly as @JordanCClark, works very well. It doesn’t need to see zero either, it will handle any rollover, assumes entries are in order though.
@JordanCClark thanks a lot, loved the way your script explained everything by itself and it works perfectly with sample dataset and handling zeros, null and counter rollover. How I can use a DB table instead of sample dataset. Really appreciate your support.
@victordcq thanks a lot for your support. I shall try to understand the way you performed to perfectly manage it by SQL but only one thing to be noticed is that its giving 100 more in total. it supposed to be 1008 but its coming 1108. I shall try to figure out this but I shall be very pleased if you can add some more to fix. I shall read about IIF and LAG as I am beginner and never used them so far. Thanks again.
Yeah i forgot to substract the first value xd And didnt get back to it since i saw Jordan had an easier answer anyways xd
Here ya go, changed the initial value to itself so it result in 0 (in second lag function)
lag(ISNULL(x,0),1,ISNULL(x,0))
select sum(diff) from (
SELECT
IIF(ISNULL(x,0)-lag(ISNULL(x,0),1,0) over (order by idx) >=0, ISNULL(x,0) - lag(ISNULL(x,0),1,ISNULL(x,0)) over (order by idx), 0) as diff
FROM [test].[dbo].[Table_1]
) t
You would need to replace it with a query to the database.
# Set start and end dates for the query.
# You will likely have to adjust this portion to fit the format of your timestamps
startDate = system.date.parse('2021-11-01 00:00:00')
endDate = system.date.parse('2021-11-02 00:00:00')
query = """SELECT *
FROM PowerMeterSeries
WHERE t_stamp >= ? and t_stamp < ? """
dataIn = system.db.runPrepQuery(query, [startDate, endDate], 'myDbConnection')
@victordcq Thanks, its really amazing to ask people here and their positive and needful support. It worked for our sample dataset but when i tried it using in actual table, it dosn’t below is the snapshot. I am trying to learn how i can eliminate it. But one thing straight forward, my example was wrong. In actual i am using energy meter totalizer and calculating consumption. when we use last current value after null it makes a huge difference in calculation. I apologies for wrong example. I didn’t noticed this in example. here is the query and outcome.
Query: SELECT
IIF(ISNULL(K360,0)-lag(ISNULL(K360,0),1,0) over (order by t_stamp) >=0, ISNULL(K360,0) - lag(ISNULL(K360,0),1,ISNULL(K360,0)) over (order by t_stamp), 0) as diff
FROM CUBGroup where t_stamp between ‘2021-10-01 08:00:00:000’ and ‘2021-10-30 09:00:00:000’
@JordanCClark Thanks a ton, will give it a try and will update here about the outcome. I should read bit more about scripting, my background is from PLC programming actually. So I am taking time to digest the plenty if information got from you and @victordcq . thanks again
Always happy to help 
Not sure if this is the best option but you can do this by first selecting the values and filter on not null.
change
FROM [test].[dbo].[Table_1]
to
FROM (SELECT
x,idx
FROM [test].[dbo].[Table_1] where x is not null)s
SELECT
x,
IIF(ISNULL(x,0)-lag(ISNULL(x,0),1,0) over (order by idx) >=0, ISNULL(x,0) - lag(ISNULL(x,0),1,ISNULL(x,0)) over (order by idx), 0) as diff
FROM (SELECT
x,idx
FROM [test].[dbo].[Table_1] where x is not null)s
So for you i guess it would be something like
FROM CUBGroup where t_stamp...
=>
FROM (select t_stamp, K360 from CUBGroup where K360 is not null)a where t_stamp...
img=(new query,
old query
sum new query)
For an other senario if there is a reset after a NULL you should change the last 0 to x
over (order by idx), 0) as diff => over (order by idx), x) as diff
fix:
Else it will start from 0 missing the first values (ofc you can still lose some values from what ever caused the null to happen)
Just a note, your queries won’t work with tag history, there is no Id/Idx.
This is a great thread, thank you to all who have contributed. I have this exact issue with an Eaton meter, and the solutions here prompted me to tackle it. Undoubtedly there are better solutions, but this what i came up with. For my report i queried the data data directly, then created a script data source to go through and calculate the difference.
queryData = data['query'].getCoreResults()
previousValues = {}
diffValue = []
diffBetweenRows = []
for row in range(queryData.getRowCount()):
group = queryData.getValueAt(row, 'SourceName')
if group in previousValues:
# do calculation
currentValue = queryData.getValueAt(row, 'Value')
diffValue = currentValue - previousValues[group]
previousValues[group] = queryData.getValueAt(row, 'Value')
if (currentValue >= queryData.getValueAt(row-1, 'Value')):
diffBetweenRows.append(diffValue)
else:
diffBetweenRows.append((999999-queryData.getValueAt(row-1, 'Value')) + currentValue)
else:
# first of the group
diffBetweenRows.append(0)
previousValues[group] = queryData.getValueAt(row, 'Value')
data['query'] = system.dataset.addColumn(queryData, diffBetweenRows, 'newValue', float)
You can change id to t_stamp too