Create a "view" using a powertable

Ignition
1

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!

2

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.

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

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.

4

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

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)
5

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