Comparing Two Datasets Values, and using those to determine difference

DatasetTestUtils compares column names, column types, row count, and actual data values in that order.

If you strictly want to compare datapoints, and don’t want to write the iteration yourself, you could cast each dataset you have into a com.inductiveautomation.ignition.common.BasicDataset, then compare them using java.util.Arrays.deepEquals() - something like this:

from java.util import Arrays 
from com.inductiveautomation.ignition.common import BasicDataset

def equals(ds1, ds2):
	if not isinstance(ds1, BasicDataset):
		ds1 = BasicDataset(ds1)

	if not isinstance(ds2, BasicDataset):
		ds2 = BasicDataset(ds2)

	return Arrays.deepEquals(ds1.getData(), ds2.getData())

The cast to BasicDataset() is necessary because BasicDataset has a getData() method that returns a 2d array of all the values in the dataset - something that is not part of the Dataset interface, and therefore will not always be available on implementing classes.

1 Like

Yes I knew this so that's why I named the data inside and Colums inside exactly the same but I get a false result. I have not tried comparing the data property of two different tables with the same data to test my theory that it's giving me false just because the dataset property being compared its named differently.i will test and let you know if that if that works for True. If it does then I'll just create and invisible power table that will.have the old data.it is not ideal but if it does trick then it's fine .

nope it does not work either, here is a photo of the my designer. i tried with two brand new Power Table. with no data on it.

@PGriffith I’m Not really sure how to do this. but to make this simple right now all i need is to know if the data was edited, nothing more than that. if a pallet was removed, or if it was changed. this is because the user will be opening the invoice, so if they edit, and then try to close the window, i need to let them know if they want to save the changes or not.

And @martinel3746 i can’t just modify the database while they are editing, because if they make a mistake to go back it’s not that easy and will be writting unnessary to the DB to many times. it just does not make sense to edit the DB until they want to save the changes they made. I figure out a way to get if they change any cases from the data, by just adding a column in the Power Table.data with the name oldcasesship. so all i do is just compare whatever the newcases ship is against the old, and i update the db with the difference. they i just do the rest on a the db. but i still need to be able to know if the new data was edited, this is not only for my powertable, but for the whole invoice form. i have and InvoiceDS, which has all the data pulled from the db like(invoiceid, customerid, shipday, appointmenttime, and much more info), but if the compare ds does not work correctly then how will i know if it was edited or not.

I know I can just add and edit button which I did, the whole idea is to make this work automatic without the user having to press and edit. if the Invoice was edit, the program needs to know by itself. the reason why i need this to work correctly it’s obvious offcourse, but i also want to do much more with that, but if i can’t compare apples to apples. this is pretty much useless for me. I want to add this code into add this code into the InternalFrameClosing, to prevent a close if the invoice was edit. i need this for many reasons , simplest one, would be user forgerts they are doing a change, and tries to close the program. this will prevent the client from closing. if there where editing. just like word, note, excel, and every single program in the world where you can edit anything. so it is very important to get this to work.

Could be done with sets. Give me a moment. Rome has been burning.

Try this one, modify the script to fit your needs:

Compare Datasets_2019-04-15_1605.proj (11.5 KB)

editTable = event.source.parent.getComponent('Edit Table')
masterTable = event.source.parent.getComponent('Master Table')

editData = editTable.data
masterData = masterTable.data

editSet =set(tuple(row) for row in system.dataset.toPyDataSet(editData))
masterSet =set(tuple(row) for row in system.dataset.toPyDataSet(masterData))

print editSet
print masterSet
print editSet - masterSet

1 Like

Thank you @JordanCClark , but how does that help in this case, as that in not coparing anything. can you explain, when you can no rush. but when you have time can you please explain how am i going to use that. let’s say i want to set a custom property name InvoiceWasChanged(Boolean). set this property to true if the data was change at all.

Sure!

editTable = event.source.parent.getComponent('Edit Table')
masterTable = event.source.parent.getComponent('Master Table')

editData = editTable.data
masterData = masterTable.data

editSet =set(tuple(row) for row in system.dataset.toPyDataSet(editData))
masterSet =set(tuple(row) for row in system.dataset.toPyDataSet(masterData))

print editSet
print masterSet
print editSet - masterSet

if len(editSet - masterSet) > 0:
  print 'A change has occurred!'

Or, in your case:

table.InvoiceWasChanged = 1

Ok, time for a breather! :slight_smile:

Subtracting on set from another removes whatever is in the second set from the first set. what remains is whatever changes were made to the edit table. If there is nothing in the subtracted set, there was no change. Otherwise you have a new set with each row that has a change in it.

Play with the sample window I supplied. change whatever you want in the edit table, and see the results.

thank you but it does not work completely, because i can add or remove rows. and i does not recognize that. it only work with one type situation, but not all. for example i just deleted a row and it does not do anything in that situation… it is better than nothing so i will see what i can do to make it work as i need to. thank you very much for all the time spent on my question.

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

Thank you very @JordanCClark you have save me a lot of trouble , and it is greatly appreciated

@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?