As written, it will find changes and additions. To hit up deletions, subtract the other way.
This will print which pallet numbers have been changed, which have been added, and which have been deleted (Use on the window provided above):
editTable = event.source.parent.getComponent('Edit Table')
masterTable = event.source.parent.getComponent('Master Table')
editData = editTable.data
masterData = masterTable.data
headers = masterData.getColumnNames()
editPyData = system.dataset.toPyDataSet(editData)
masterPyData = system.dataset.toPyDataSet(masterData)
editSet =set(tuple(row) for row in editPyData)
masterSet =set(tuple(row) for row in masterPyData)
changes = [list(row) for row in (editSet-masterSet)]
deletions = [list(row) for row in (masterSet-editSet)]
# get original palletNumbers
masterIndices = [row[0] for row in masterPyData]
# pallet numbers edited
changeIndices = [row[0] for row in changes if row[0] in masterIndices]
# pallet numbers added
addIndices = [row[0] for row in changes if row[0] not in masterIndices]
# pallet numbers deleted
deleteIndices = [row[0] for row in deletions if row[0] not in changeIndices]
print 'Pallets edited: ', changeIndices
print 'Pallets added: ', addIndices
print 'Pallets deleted: ', deleteIndices
if (len(changeIndices) + len(addIndices) + len(deleteIndices)) > 0:
print 'Edits have occurred!'
1 Like
@JordanCClark I have a similar application, but am not certain if I can utilize some of the above scripting for my case. We have two tables that we are wanting to compare and output the differences (one from SQL, one uploaded from excel/csv which can be pushed to another SQL table first if that makes it easier).
Each table will have 3 columns: part#, location, and qty. Based on the part# weād like to know that first thereās no duplicates (more of a concern in the excel/csv) and the locations match. Second, weād like to know if the quantities differ (and if possible place a tolerance in for that).
Would the set tuple method listed above be able to let us dive into individual cell level to run this audit process? Appreciate any feedback you can offer to help as a starting point. Thanks.
For deeper analysis, you may want to consider switching to dictionaries. Sets are great for looking for differences, but actual lookups are better in dictionaries.
If you can post or PM a sample file (csv is okay) I can take a deeper look for you.
I was given some suggestions from coworkers at another site that may work so I will begin with those. I also reviewed with the applicationās requestors on the point of taking an export of the SQL table and doing their comparison in excel instead so if neither of those works out I will let you know if I am still needing assistance. Thank you!
1 Like
As it's been pointed before, DatasetTestUtils.equals is not working as intended.
I've used the example found in the docs:
import system.dataset
from com.inductiveautomation.ignition.common.util import DatasetTestUtils
from java.util import Arrays
header = ['myColumn']
rows = [[1], [2]]
dataset = system.dataset.toDataSet(header, rows)
another_dataset = system.dataset.toDataSet(header, rows)
DatasetTestUtils.equals(dataset, another_dataset) # returns False
Arrays.deepEquals(dataset.getData(), another_dataset.getData()) # returns True
DatasetTestUtils.equals(dataset, dataset) # returns True; but what's the point?
The only way I could get a True from DatasetTestUtils.equals() is if I pass the same Dataset as arguments.
Can we flag this as a bug, @PGriffith?
Well, that's fun. I don't know if maybe Jython 2.5 behaves differently than 2.7 here, but DatasetTestUtils.equals() doesn't give you a useful result because it's not being called. Jython is invoking the base Object method, which is why you're getting referential equality behavior. I can't see any way to convince Jython to invoke the static equals method correctly. @pturmel may have some ideas?
I don't think it's a bug (the behavior of the method itself is fine) outside of perhaps a bug in Jython, but fixing it is not likely to be fun or low risk. I think your best options are to 1. script this yourself or 2. use Ignition Extensions: Ignition Extensions - Convenience utilities for advanced users
1 Like
Jython doesn't distinguish different method signatures by argument types, but only by number of arguments. I believe the method with the widest type acceptance overall will be the signature encoded for that method. This is true for instance methods, too.
You can work around this with explicit wrapper types that will delegate to one of several signatures at runtime. Java always picks from among possible method signatures at compile-time. Jython obviously cannot do that.
Hello ,can u help with the following?
I am running a simple sql query in ignition and returning values to the pydataset. i want to compare the returned values from a column to a number inputted into a ignition project to see if the inputted value matches with the query dataset values how does the scripting work for these?
Please start a new topic. (And don't use any indent on your description--it makes the post formatting weird and unreadable.)