I have a table with running totals in one column and the time the total was taken in another column. I want to graph the difference between n and n-1 in a bar-graph. An additional obstacle is that the time-range overlaps two days (starts at 10:00 PM and ends at 10:00 PM the following day). I have been scratching my head on this one for a while…Any easy way to do this?
[quote=“jvandeve”]Any easy way to do this?[/quote] I think that a stored procedure is the only way to do this (someone correct me if I’m wrong). You could also bring the data in and calculate the deltas in a FactoryPMI script, but you posted this in the FactorySQL forum - I’m not sure if you’re using FactoryPMI or not. You choose - Stored Procedure or FactoryPMI script, and we can help you with either. Although, a FactoryPMI script will be easier for us to come up with, of course…
I am using FactoryPMI and that would be my preferred solution as well.
Ok, here is the idea. I’ve put two custom properties on the root container of my window, called “rawData” and “deltadata”. They are both DataSets. I bind the “rawData” one to a SQL query that brings in the running total data in two columns - first one is the timestamp and the second one is the running total. Then I put the following script on the propertyChange event of the root container. The script will run every time new data comes into the “rawData” property, will convert the data into deltas, and will assign the new dataset into the “deltaData” property. You would then drive your bar chart or table or whatever you want using the “deltaData” property.
Here is the script:
if event.propertyName=='rawData':
raw = fpmi.db.toPyDataSet(event.newValue)
delta = []
lastValue = None
for row in raw:
if not lastValue:
# first row - initialize last value variable
lastValue=row[1]
else:
delta.append([row[0], row[1]-lastValue])
lastValue=row[1]
newDataSet = fpmi.db.toDataSet(["t_stamp", "delta"], delta)
event.source.deltaData = newDataSet
Clearly, you can alter names, locations of variables, etc to your liking. If you need help formulating the SQL query to drive the raw data (its just date manipulation to get 10PM-10PM on two days, not a big deal), let us know.
Hope this helps,