# Create a "view" using a powertable

**URL:** <https://forum.inductiveautomation.com/t/create-a-view-using-a-powertable/57328>\
**Category:** Ignition\
**Created:** [March 17, 2022, 6:53pm UTC](https://forum.inductiveautomation.com/t/create-a-view-using-a-powertable/57328 "2022-03-17T18:53:12Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![gregory.sachs](https://avatars.discourse-cdn.com/v4/letter/g/e47774/32.png) [@gregory.sachs](https://forum.inductiveautomation.com/u/gregory.sachs)\
**Post date:** [March 17, 2022, 6:53pm UTC](https://forum.inductiveautomation.com/t/create-a-view-using-a-powertable/57328/1 "2022-03-17T18:53:12Z")

</div>

I have a power table that queries a DB and one column returns a numeric error code.  
I’d like to create a dataset tag that has a numeric column and a text column, to cross reference value X=Station 10 Failure and so on. I have done something similar with scripting and lookup(), but I’m not sure how to do it with python, most explanations seem to suggest using pandas which I don’t think is available.  
Ideally this would be done by just making a child table in the DB and a view I could query, but thus far I haven’t been able to get that allowed.  
Thanks!

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [March 17, 2022, 7:05pm UTC](https://forum.inductiveautomation.com/t/create-a-view-using-a-powertable/57328/2 "2022-03-17T19:05:30Z")

</div>

Create another DB table that has your cross reference data in it, then join that table to your original query. For example, if I have a table `foo` which contains a column `error_code` then I have a table `bar` that has `error_desc`.

```auto
select f.error_code, b.error_desc
from foo f
left join bar b on b.error_code = f.error_code
where ...

```

---

<div class="post-metadata">

**Author:** ![gregsachs](https://avatars.discourse-cdn.com/v4/letter/g/58f4c7/32.png) [@gregsachs](https://forum.inductiveautomation.com/u/gregsachs)\
**Post date:** [March 17, 2022, 8:27pm UTC](https://forum.inductiveautomation.com/t/create-a-view-using-a-powertable/57328/3 "2022-03-17T20:27:47Z")

</div>

That would also be a solution, it is functionally the same as my ideal solution of using a DB view that does the join. At this time I’m not being permitted to make a change to the DB. So is there an ignition function to do a join on two datasets? I could easily create a dataset tag with my cross reference values, but then i need to cross it to the powertable dataset.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [March 17, 2022, 8:33pm UTC](https://forum.inductiveautomation.com/t/create-a-view-using-a-powertable/57328/4 "2022-03-17T20:33:28Z")

</div>

Yes you can do it with scripting, but it will be less efficient than doing it in a query.

Here is some code written by @JordanCClark

```auto
def combineDatasets(dataList, commonCol):
	''' Combine multiple datasets based on a common column
		dataList: list of datasets
		commonCol: column name common to all datasets	    
	'''
	# Convert all datsets to BasicDataset, if needed
	for i, data in enumerate(dataList):
		if 'com.inductiveautomation.ignition.common.BasicDataset' not in str(type(data)):
			dataList[i] = system.dataset.toDataSet(data)
	
	# Create default value dictionary containing all column names
	# with None values
	blankValueDict = {}
	for data in dataList:
		colNames = list(data.getColumnNames())
		for col in colNames:
			if col != commonCol and col not in blankValueDict.keys():
				blankValueDict[col] = None

	# Process the data
	dataDict = {}
	for data in dataList:
		colNames = list(data.getColumnNames())
		for i in xrange(data.rowCount):
			commonColValue = data.getValueAt(i, commonCol)
			if commonColValue not in dataDict.keys():
				dataDict[commonColValue] = blankValueDict.copy()
			for col in colNames:
				if col != commonCol:
					dataDict[commonColValue][col] = data.getValueAt(i, col)

	# Create combined dataset
	headers = [commonCol] + sorted(blankValueDict.keys())
	data = []
	for key in sorted(dataDict.keys()):
		newRow=[]
		newRow.append(key)
		for col in headers[1:]:
			newRow.append(dataDict[key][col])
		data.append(newRow)
	
	return system.dataset.toDataSet(headers, data)

```

---

<div class="post-metadata">

**Author:** ![gregsachs](https://avatars.discourse-cdn.com/v4/letter/g/58f4c7/32.png) [@gregsachs](https://forum.inductiveautomation.com/u/gregsachs)\
**Post date:** [March 21, 2022, 6:25pm UTC](https://forum.inductiveautomation.com/t/create-a-view-using-a-powertable/57328/5 "2022-03-21T18:25:11Z")

</div>

Thanks-that does look like it is involved. Will continue pushing to do this the right way with a view.