# Filter Dataset Based on Column Value

**URL:** <https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356>\
**Category:** Ignition\
**Tags:** scripting\
**Created:** [October 18, 2022, 7:09pm UTC](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356 "2022-10-18T19:09:29Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Isaac\_Hodgkins](https://avatars.discourse-cdn.com/v4/letter/i/59ef9b/32.png) [@Isaac\_Hodgkins](https://forum.inductiveautomation.com/u/Isaac_Hodgkins)\
**Post date:** [October 18, 2022, 7:09pm UTC](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356/1 "2022-10-18T19:09:29Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/b/eba00faaa6befcdf9ff8208c84bd7bd3f5fc2895.png)  
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](https://docs.inductiveautomation.com/display/DOC80/Datasets)  
There didn't seem to be a command for returning a row index or for searching by a value.

Thank you for your time!

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [October 18, 2022, 8:38pm UTC](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356/2 "2022-10-18T20:38:23Z")

</div>

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:

```python
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:

```python
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)

```

```auto
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](https://forum.inductiveautomation.com/t/ignition-extensions-convenience-utilities-for-advanced-users/64615) but is just useful for testing, not necessary.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 18, 2022, 9:17pm UTC](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356/3 "2022-10-18T21:17:34Z")

</div>

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

```auto
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](https://inductiveautomation.com/moduleshowcase/module/automation-professionals-llc-simulation-aids) module.

> [@Isaac\_Hodgkins](#):
>
> There didn't seem to be a command for returning a row index or for searching by a value.

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

---

<div class="post-metadata">

**Author:** ![Isaac\_Hodgkins](https://avatars.discourse-cdn.com/v4/letter/i/59ef9b/32.png) [@Isaac\_Hodgkins](https://forum.inductiveautomation.com/u/Isaac_Hodgkins)\
**Post date:** [October 19, 2022, 2:58pm UTC](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356/4 "2022-10-19T14:58:32Z")

</div>

> [@paul-griffith](#):
>
> ```auto
> 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 |
> 
> ```

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

---

<div class="post-metadata">

**Author:** ![Isaac\_Hodgkins](https://avatars.discourse-cdn.com/v4/letter/i/59ef9b/32.png) [@Isaac\_Hodgkins](https://forum.inductiveautomation.com/u/Isaac_Hodgkins)\
**Post date:** [October 19, 2022, 3:01pm UTC](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356/5 "2022-10-19T15:01:15Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 19, 2022, 3:35pm UTC](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356/6 "2022-10-19T15:35:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Isaac\_Hodgkins](https://avatars.discourse-cdn.com/v4/letter/i/59ef9b/32.png) [@Isaac\_Hodgkins](https://forum.inductiveautomation.com/u/Isaac_Hodgkins)\
**Post date:** [October 19, 2022, 4:10pm UTC](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356/7 "2022-10-19T16:10:41Z")

</div>

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

```auto
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/e/7ed018649e9ea8fb704f4ca5b447acf7955b8148.png)

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [October 19, 2022, 4:11pm UTC](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356/8 "2022-10-19T16:11:27Z")

</div>

`view` is an _expression_, not a scripting function.

Use an expression transform.

---

<div class="post-metadata">

**Author:** ![Isaac\_Hodgkins](https://avatars.discourse-cdn.com/v4/letter/i/59ef9b/32.png) [@Isaac\_Hodgkins](https://forum.inductiveautomation.com/u/Isaac_Hodgkins)\
**Post date:** [October 19, 2022, 4:30pm UTC](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356/9 "2022-10-19T16:30:49Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/0/70cba49eeb3ef9e629d8acc9f3154c0f0019f902.png)

```auto
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?

---

<div class="post-metadata">

**Author:** ![Matthew.gaitan](https://avatars.discourse-cdn.com/v4/letter/m/59ef9b/32.png) [@Matthew.gaitan](https://forum.inductiveautomation.com/u/Matthew.gaitan)\
**Post date:** [October 19, 2022, 4:32pm UTC](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356/10 "2022-10-19T16:32:12Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [October 19, 2022, 4:44pm UTC](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356/11 "2022-10-19T16:44:40Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Isaac\_Hodgkins](https://avatars.discourse-cdn.com/v4/letter/i/59ef9b/32.png) [@Isaac\_Hodgkins](https://forum.inductiveautomation.com/u/Isaac_Hodgkins)\
**Post date:** [October 19, 2022, 4:49pm UTC](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356/12 "2022-10-19T16:49:53Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/2/62b0e79dabfcaee450798daacb278e3bbffeab36.png)  
Yes it's running. And I restarted my designer as well.

---

<div class="post-metadata">

**Author:** ![Isaac\_Hodgkins](https://avatars.discourse-cdn.com/v4/letter/i/59ef9b/32.png) [@Isaac\_Hodgkins](https://forum.inductiveautomation.com/u/Isaac_Hodgkins)\
**Post date:** [October 19, 2022, 4:56pm UTC](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356/13 "2022-10-19T16:56:02Z")

</div>

I tried single quotes but that didn't work either.  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/2/e2c5c81d1fa96c4ff024e268c75cda227b7bb9c4.png)  
Here's an image of my values. machineName and DSHW001 match

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

```

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

---

<div class="post-metadata">

**Author:** ![Matthew.gaitan](https://avatars.discourse-cdn.com/v4/letter/m/59ef9b/32.png) [@Matthew.gaitan](https://forum.inductiveautomation.com/u/Matthew.gaitan)\
**Post date:** [October 19, 2022, 5:20pm UTC](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356/14 "2022-10-19T17:20:03Z")

</div>

seems to work for me:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/6/464ce69413935fef53744931ab54bbe98d984c7e.png)

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

---

<div class="post-metadata">

**Author:** ![Isaac\_Hodgkins](https://avatars.discourse-cdn.com/v4/letter/i/59ef9b/32.png) [@Isaac\_Hodgkins](https://forum.inductiveautomation.com/u/Isaac_Hodgkins)\
**Post date:** [October 19, 2022, 6:41pm UTC](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356/15 "2022-10-19T18:41:44Z")

</div>

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

```auto
{[.]usf2Query}

```

Then it works and shows the full dataset, so I know the tag path is correct.  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/8/d883d7bde9ca30a2a405e20df95e46161a4b7f7c.png)

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

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

```

I also tried removing everything but select.

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

```

Still getting Error\_Configuration  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/1/21ebebcc213e1a43949b1c4decf9789ecc9fae5d.png)

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

---

<div class="post-metadata">

**Author:** ![Matthew.gaitan](https://avatars.discourse-cdn.com/v4/letter/m/59ef9b/32.png) [@Matthew.gaitan](https://forum.inductiveautomation.com/u/Matthew.gaitan)\
**Post date:** [October 19, 2022, 6:49pm UTC](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356/16 "2022-10-19T18:49:01Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/f/bf833b7bba2ab4f30e95c326ca7d4bd0d28911d7.png)

---

<div class="post-metadata">

**Author:** ![Isaac\_Hodgkins](https://avatars.discourse-cdn.com/v4/letter/i/59ef9b/32.png) [@Isaac\_Hodgkins](https://forum.inductiveautomation.com/u/Isaac_Hodgkins)\
**Post date:** [October 19, 2022, 7:08pm UTC](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356/17 "2022-10-19T19:08:39Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/a/7a13d3c7d526eba84cefb0b4426c32855e77a3bb.png)  
It looks like it doesn't recognize the view function, so there must be a problem with the way I installed it?

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/d/2dea55c8bee7c834dc242adc2259fe65573ad576.png)  
I installed version 8.1 of Simulation Aids.  
It says it's running on my Ignition configure module page.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 19, 2022, 7:10pm UTC](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356/18 "2022-10-19T19:10:29Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Isaac\_Hodgkins](https://avatars.discourse-cdn.com/v4/letter/i/59ef9b/32.png) [@Isaac\_Hodgkins](https://forum.inductiveautomation.com/u/Isaac_Hodgkins)\
**Post date:** [October 19, 2022, 7:38pm UTC](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356/19 "2022-10-19T19:38:14Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/3/b3bb44040ee542c40497e91323d74470ec8616ad.png)  
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?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 19, 2022, 7:58pm UTC](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356/20 "2022-10-19T19:58:30Z")

</div>

No, that's the right place.

[Next page](https://forum.inductiveautomation.com/t/filter-dataset-based-on-column-value/65356.md?page=2)