Filter Dataset Based on Column Value

Ignition
1

I'm using a query tag to get a dataset that is 14 rows and 9 columns.
Here's the dataset in a table.

image
image1065×286 27.7 KB

I'd like to use a script to filter the dataset down to one row and pass it to another tag. So for example I could filter it by machine name to DSHW001 and activity type to ISSUE so that way it passes a dataset of 1 row and 9 columns to the next tag.
I looked through the dataset documentation here
There didn't seem to be a command for returning a row index or for searching by a value.

Thank you for your time!

2

There's lots of ways to skin this cat, but if you truly want to do it from scripting, it'll probably be easiest if you convert to a PyDataset first.
If all your conditions are simple literal values, you can do something like this:

def filterDataset(ds, **kwargs):
	pds = system.dataset.toPyDataSet(ds)
	output = []
	for row in pds:
		if all(row[key] == value for key, value in kwargs.items()):
			output.append(row)
	
	return system.dataset.toDataSet(list(ds.columnNames), output)

E.G:

def sampleData(rows=10):
	from random import choice, randint
	
	machines = [
		"DSHW0001",
		"DSHW0002",
		"DSHW0003",
		"FGHW0001",
		"FGHW0002",
		"FGHW0003",
		"MCHW0001",
		"MCHW0002",
		"MCHW0003",
	]
	activityTypes = [
		"ISSUE",
		"PROD",
	]
	data = [
		[choice(machines), choice(activityTypes), system.date.now(), "ABC{:05}".format(randint(0, 100))]
		for _ in xrange(rows)
	]
	
	headers = ["machineName", "activityType", "productionDate", "orderNumber"]
	return system.dataset.toDataSet(headers, data)

ds = sampleData()
print "input:"
system.dataset.print(ds)

def filterDataset(ds, **kwargs):
	pds = system.dataset.toPyDataSet(ds)
	output = []
	for row in pds:
		if all(row[key] == value for key, value in kwargs.items()):
			output.append(row)
	
	return system.dataset.toDataSet(list(ds.columnNames), output)
	

filtered = filterDataset(ds, machineName="DSHW0001", activityType="ISSUE")
print "filtered:"
system.dataset.print(filtered)

input:
| Row | machineName | activityType |               productionDate | orderNumber |
| --- | ----------- | ------------ | ---------------------------- | ----------- |
|   0 |    FGHW0001 |        ISSUE | Tue Oct 18 20:35:23 UTC 2022 |    ABC00054 |
|   1 |    MCHW0001 |        ISSUE | Tue Oct 18 20:35:23 UTC 2022 |    ABC00073 |
|   2 |    FGHW0002 |         PROD | Tue Oct 18 20:35:23 UTC 2022 |    ABC00096 |
|   3 |    FGHW0003 |         PROD | Tue Oct 18 20:35:23 UTC 2022 |    ABC00053 |
|   4 |    DSHW0001 |        ISSUE | Tue Oct 18 20:35:23 UTC 2022 |    ABC00067 |
|   5 |    FGHW0002 |        ISSUE | Tue Oct 18 20:35:23 UTC 2022 |    ABC00021 |
|   6 |    MCHW0001 |         PROD | Tue Oct 18 20:35:23 UTC 2022 |    ABC00002 |
|   7 |    MCHW0001 |         PROD | Tue Oct 18 20:35:23 UTC 2022 |    ABC00059 |
|   8 |    FGHW0002 |        ISSUE | Tue Oct 18 20:35:23 UTC 2022 |    ABC00094 |
|   9 |    FGHW0001 |         PROD | Tue Oct 18 20:35:23 UTC 2022 |    ABC00071 |
filtered:
| Row | machineName | activityType |               productionDate | orderNumber |
| --- | ----------- | ------------ | ---------------------------- | ----------- |
|   0 |    DSHW0001 |        ISSUE | Tue Oct 18 20:35:23 UTC 2022 |    ABC00067 |

system.dataset.print comes from Ignition Extensions but is just useful for testing, not necessary.

3 Likes
3

For some reason, I'm rather partial to the view() expression function:

view("SELECT * WHERE machineName==args[0] AND activityType==args[1]",
  {path/to/base/dataset/tag}, {path/to/desired/machine}, {path/to/desired/activity})

From the free Simulation Aids module.

That's one of the reasons this module exists. (:

5 Likes
4

Thank you so much for your response! I'll give this a go!

5

Wow, this seems much more concise than scripting with python, and I can do it in SQL this way. I'll try that module. Thank you!

1 Like
6

Just beware that it is Pseudo-SQL, not real SQL. Note the double-equals--expressions are python syntax, not SQL.

7

I can't quite seem to get this to work.

def valueChanged(tag, tagPath, previousValue, currentValue, initialChange, missedEvents):
	"""
	Fired whenever the current value changes in value or quality.

	Arguments:
		tag: The source tag object. A read-only wrapper for obtaining tag
		     properties.
		tagPath: The full path to the tag (String)
		previousValue: The previous value. This is a "qualified value", so it
		               has value, quality, and timestamp properties.
		currentValue: The current value. This is a "qualified value", so it has
		              value, quality, and timestamp properties.
		initialChange: A boolean flag indicating whether this event is due to
		               the first execution or initial subscription.
		missedEvents: A flag indicating that some events have been skipped due
		              to event overflow.
	"""
	filteredData = view("SELECT * WHERE machineName==DSHW001 AND activityType==ISSUE", currentValue.value)
	system.tag.writeBlocking("[.]usft2Filter", filteredData)

If I just use system.tag.writeBlocking("[.]usft2Filter", currentValue.value) it works fine, so I must be making a mistake with the view() expression. I installed the module and reset my designer.

image
image1166×160 10.1 KB

8

view is an expression, not a scripting function.

Use an expression transform.

1 Like
9

image

view("SELECT * WHERE machineName==DSHW001 AND activityType==ISSUE", {[.]usf2Query})

I'm getting a configuration error. I noticed it's not making view() bold like it normally does for functions. Is it possible the module is not running on my project?

10

try wrapping DSHW001 and ISSUE with single quotes, assuming machineName and activityType are your column names, and the other two are the values you want them to equal

11

Go to you're gateway->Config->Modules page. Do you see the simulation aids module is running?

12

image
image1184×175 10.2 KB

Yes it's running. And I restarted my designer as well.

13

I tried single quotes but that didn't work either.
image
Here's an image of my values. machineName and DSHW001 match

view("SELECT * WHERE machineName=='DSHW001'", {[.]usf2Query})

I tried making my expression simpler to get it to work.

14

seems to work for me:

image
image1549×487 60.4 KB

view('select * WHERE name == "Location 1"', {[.]New Tag})

is it erroring out?
is the expression tag the correct data type? (dataset)

also, i assumed you were using a UDT based on your tag reference. Are you using a UDT, or are you using 2 separate tags?

15

I'm using a UDT
Both tags are set to dataset. If I change the expression to just

{[.]usf2Query}

Then it works and shows the full dataset, so I know the tag path is correct.
image

I tried formatting it exactly like yours and I still got the error.

view('select * WHERE machineName=="DSHW001"', {[.]usf2Query})

I also tried removing everything but select.

view('select *', {[.]usf2Query})

Still getting Error_Configuration
image

It almost feels as if the module isn't actually running, but again it says it is under the modules config page.

16

if you open the tag editor and open the Show Diagnostics pane, what does it show for the error?

image
image879×755 55.3 KB

17

Oh nice, I was not aware of that of the diagnostic tool.

image
image706×141 15.4 KB

It looks like it doesn't recognize the view function, so there must be a problem with the way I installed it?

image
image1870×358 45.1 KB

I installed version 8.1 of Simulation Aids.
It says it's running on my Ignition configure module page.

18

Restart the module while watching your gateway log. There's probably something there that I'll need to fix.

19

image
image916×248 13.5 KB

I checked the logs located under status on the gateway. You can see it restarting the module but it doesn't give any errors. Is there a different place I should be looking?

20

No, that's the right place.