How to filter a dataset to a new dataset

Ignition
,
1

Hello :smiley:
My Ignition Vision is 7.9.16.
I want to query a dataset (This dataset just have ID and another string type data) to a new dataset (same structure as old one). The rule of filter is ’ while the string data (old dataset) exists in a Database, it should be displayed in new dataset '.
What kind of sulotion can I use and How to use? Thanks in advance! :smiley:

2

Have to say I’m a bit confused by your question. It isn’t exactly clear what you’re trying to do.

What exactly have you tried and not gotten the results you’re looking for? What is the difference between the old dataset and the new dataset?

1 Like
3 
def databaseCheck(itemToSearch):
	
	databaseFakeData = ["c","d","e"]
	return itemToSearch in databaseFakeData
	


header = ["key","value"]
body = [[1, 'a'],[2, 'b'],[3, 'c'],[4, 'd'],[5, 'e']]

oldDataset = system.dataset.toDataSet(header, body)
oldDataset = system.dataset.toPyDataSet(oldDataset)

newBody = []

for item in oldDataset :
	
	if databaseCheck(item["value"]):
		newBody.append([item['key'], item['value']])

newDataset = system.dataset.toDataSet(header, newBody)
4

@pturmel’s (free) Simulation Aids module adds a view expression function that allows you to query a dataset using SQL-like syntax:
https://www.automation-pros.com/simaids/doc/expression.view.html

1 Like
5

I didn’t pipe up immediately with a suggestion to use my view() function because you need a separate query against the DB to lookup up the strings’ existence. A query with a dynamic IN clause of strings. Non-trivial.

6

Thanks all replies, I add a button and script it:

#Manual inserted table in HMI
InsertedTable = event.source.parent.getComponent(“DatasetEditor”).data
#get the needed col
pData = system.dataset.filterColumns(InsertedTable,[1])
IDlist = system.dataset.toPyDataSet(pData)
containerList = system.db.runQuery(“SELECT barcode FROM location”,“DB”)

dblist=
for row in containerList:
for value in row:
dblist.append(value)

headers = [“ID”]
data =
for row in IDlist:
for value in row:
print(value)
if (value in dblist):
data.append([value])
event.source.parent.getComponent(‘Power Table 1’).data = system.dataset.toDataSet(headers, data)

And then this was solved.