Hi i am getting selected tags in list… so its easy for me to run for loop
then you should have no problem implementing what i just send^^
1 Like
@prasath.t is appending his queries into a single dataset. That actually simplifies things.
EDIT: added functionality to use None if all values in a cloumn are None.
sampleHeaders = ['date','elec','gas']
sampleData = [
['2021-11-23 00:00:00', None, 40],
['2021-11-23 00:00:00', 24, 90],
['2021-11-23 00:00:00', 89, None]
]
# Simulate the dataset used to gather the data
data = system.dataset.toDataSet(sampleHeaders, sampleData)
# Set allowed types --java.lang class names--
allowedTypes = ['Long', 'Integer', 'Double', 'Float']
# Get column names
colNames = list(data.getColumnNames())
dataOut = []
for colNum in range(data.getColumnCount()):
# print colNum, colNames[colNum], data.getColumnType(colNum).__name__
# Check that the column is one that should be summed
if data.getColumnType(colNum).__name__ in allowedTypes:
# Get column values
colValues = data.getColumnAsList(colNum)
#Use None if all values in the list are None
if all(value is None for value in colValues):
dataOut.append(None)
else:
# Sum the values and add to the row. filter() lets us ignore None
dataOut.append(sum(filter(None, colValues)))
# Otherwise, use the first row value
else:
dataOut.append(data.getValueAt(0, colNum))
datasetOut = system.dataset.toDataSet(colNames, [dataOut])
row | date | elec | gas
--------------------------------------
0 | 2021-11-23 00:00:00 | 113 | 130
1 Like
I tired your script but i am not getting the actual output. Please check the screenshot i have attached.
None values of 2 dataset added in your script i am getting 0 zero as output… but i want to return none
only i want to return integer value if one row have value other row have none value
Please give me solution for this one
hi @victordcq
Tired your script but got error
d1 = system.dataset.toDataSet(["1","2","3","4"],[[10,None,None,40]])
d2 = system.dataset.toDataSet(["1","2","3","4"],[[60,70,80,90]])
given 2 datset like this with none value in integer Column
columnData = [int(unicode) for unicode in AllRow.getColumnAsList(AllRow.getColumnIndex(col))]
getting error in this line
i am getting none value in integer column while doing query
ups right
columnData = [int(unicode or 0) for unicode in AllRow.getColumnAsList(AllRow.getColumnIndex(col))]
this will replace the none with 0, i suppose the filter later isnt needed anymore then if you care for the tiny performance
d3 = system.dataset.setValue(d3,0,col,sum(columnData))
d1 = system.dataset.toDataSet(["1","2","3","4"],[[10,None,None,40]])
d2 = system.dataset.toDataSet(["1","2","3","4"],[[60,44,None,90]])
for example - if both dataset of column 3 has none … so i want to return none to new dataset … i don’t want to get 0 zero as value
is it possible?
not like this no you would have to put in a different check to see if all data in the column is none
with any() or all() and i guess
if any(x == None for x in AllRow.getColumnAsList(AllRow.getColumnIndex(col))):
d3 = system.dataset.setValue(d3,0,col,None)
Ok i will try this
in which line i have to use this script in your code ?
Edit elif probably isnt gona work afterall just use an if after i guess
if all(x == None for x in AllRow.getColumnAsList(AllRow.getColumnIndex(col))):
d3 = system.dataset.setValue(d3,0,col,None)
1 Like
selectedDatasets =[[[10, 20, None, None]], [[10, 20, None, 40]]]
d3 = system.dataset.toDataSet(d1.getColumnNames().toArray(),[[0] * d1.getColumnCount()])
AllRow = system.dataset.toDataSet(d1.getColumnNames().toArray(),[])
for x in selectedDatasets:
AllRow = system.dataset.addRow(AllRow, x[0])
for col in AllRow.getColumnNames():
if col != "2":
if any(x == None for x in AllRow.getColumnAsList(AllRow.getColumnIndex(col))):
print '...dd..'
d3 = system.dataset.setValue(d3,0,col,None)
else:
columnData = [int(unicode or 0) for unicode in AllRow.getColumnAsList(AllRow.getColumnIndex(col))]
d3 = system.dataset.setValue(d3,0,col,sum(columnData))
else:
d3 = system.dataset.setValue(d3,0,col,AllRow.getValueAt(0,col))
print d3.getValueAt(0,3)
tried like this but column 4 of first dataset has None and column 4 second dataset have value 40
but its returning None… instead of 40
if condition should execute only all rows have none value
any correction to be done to make it work?
any should be all . i had already edited my code but seems you missed it
1 Like
thanks now its fine
[u'date', u'gasnm3', u'electricitykwh', u'gaskwh', u'productionm2', u'eleckwhm2', u'gaskwhm2', u'epikwhm2']
[u'2021-12-06', None, 3483, None, 618, 6, None, None]
2021-12-06 None 3483 None 618 6 None None
12:17:15.308 [AWT-EventQueue-0] ERROR com.inductiveautomation.ignition.client.util.gui.ErrorUtil - <HTML>Error executing script for event: <code><b>mouseReleased</b></code><BR>on component: <code><b>Button_Preview</b></code>.
com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last):
File "<event:mouseReleased>", line 340, in <module>
IndexError: Column 2 in dataset 2 with type Integer can not be cast to String
at org.python.core.Py.IndexError(Py.java:280)
at com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities.appendDataset(DatasetUtilities.java:1259)
at jdk.internal.reflect.GeneratedMethodAccessor180.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.base/java.lang.reflect.Method.invoke(Unknown Source)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:188)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:206)
at org.python.core.PyObject.__call__(PyObject.java:497)
at org.python.core.PyObject.__call__(PyObject.java:501)
at org.python.pycode._pyx2073.f$0(<event:mouseReleased>:404)
at org.python.pycode._pyx2073.call_function(<event:mouseReleased>)
at org.python.core.PyTableCode.call(PyTableCode.java:171)
at org.python.core.PyCode.call(PyCode.java:18)
at org.python.core.Py.runCode(Py.java:1614)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:799)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:206)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:297)
at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:57)
at com.sun.proxy.$Proxy47.mouseReleased(Unknown Source)
at java.desktop/java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)
at java.desktop/java.awt.Component.processMouseEvent(Unknown Source)
at java.desktop/javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.desktop/java.awt.Component.processEvent(Unknown Source)
at java.desktop/java.awt.Container.processEvent(Unknown Source)
at java.desktop/java.awt.Component.dispatchEventImpl(Unknown Source)
at java.desktop/java.awt.Container.dispatchEventImpl(Unknown Source)
at java.desktop/java.awt.Component.dispatchEvent(Unknown Source)
at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.desktop/java.awt.Container.dispatchEventImpl(Unknown Source)
at java.desktop/java.awt.Window.dispatchEventImpl(Unknown Source)
at java.desktop/java.awt.Component.dispatchEvent(Unknown Source)
at java.desktop/java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.desktop/java.awt.EventQueue$4.run(Unknown Source)
at java.desktop/java.awt.EventQueue$4.run(Unknown Source)
at java.base/java.security.AccessController.doPrivileged(Native Method)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.desktop/java.awt.EventQueue$5.run(Unknown Source)
at java.desktop/java.awt.EventQueue$5.run(Unknown Source)
at java.base/java.security.AccessController.doPrivileged(Native Method)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.desktop/java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.desktop/java.awt.EventDispatchThread.run(Unknown Source)
Caused by: org.python.core.PyException: Traceback (most recent call last):
File "<event:mouseReleased>", line 340, in <module>
IndexError: Column 2 in dataset 2 with type Integer can not be cast to String
When i try to append 2021-12-06 None 3483 None 618 6 None None this line to dataset i am getting this error in your script
datasetOut = system.dataset.toDataSet(colNames, [dataOut]) after passing to dataset i am trying to append
First row i have append with this line - 2021-12-05 None None None 669 None None None
when i try to apppend this line - 2021-12-06 None 3483 None 618 6 None None i am getting this error
line i am using to append - data1 = system.dataset.appendDataset(data1, datasetOut)
how to fix this error?
any better way to append to rows to dataset?
You are writing a string to the dataset of type integer or the other way around. Make sure everything is the correct type
yaa thats why i am confused its quering from database… I didn’t done any changes… is there any way to caste string to integer in dataset?
is the database str or number?
hehe it might be thinking the column is string because the first values are None…
why are you using append now instead of just creating headers and rows?
yes you are correct… first time its string and second time its interger that why error is coming
based on the for loop if want to get the data for 2 days
i want to append 2 days data to dataset and i am passing it to the tabel in report
thats why i am appending
Then i suggest you make the first row just base types.(with trival values) to then delete the first row once done.
idk why its not possible to define the type of a dataset an other way, it seems lke the toDataSet() function should get a 3third (optional) parameter for an array of types