Joining two ignition tables (not sql tables)

Ignition
1

Hello there,

I have two table son one ignition window. I am trying to join the two tables but getting the error- Invalid object name for tables. How do I select the columns if there is an error finding the table itself?

Any help? Below is the error

===
Exception: Error running query:
SQLQuery(query=SELECT IDandName.FaultName,
IDandCount.FaultCount
FROM IDandCount
INNER JOIN IDandName ON IDandCount.FaultID = IDandName.FaultID, database=MORPSQL_Ignite)@0ms
On: CIPFaults.Root Container.NameAndCount.data
caused by GatewayException: Invalid object name ‘IDandCount’.
caused by SQLServerException: Invalid object name ‘IDandCount’.

Ignition v7.9.7 (b2018032914)
Java: Oracle Corporation 1.8.0_231

2

Are you trying to run a query on a vision table? That is not possible to do. You need to iterate through the data properties of the tables.

1 Like
3

To expand on Jordan’s answer, the data for your Vision tables is a dataset. You can append one dataset to another using system.dataset.appendDataset()

4

If you just want to combine the data from both tables into a single dataset, you can script a propertyChange event and append the datasets using system.dataset.appendDataset(). This requires that both datasets columns match.

5

Thanks guys. I have tried system.dataset.appendDataset() but it says there is an error in Line1 char6, which is “.”. I really don’t understand this error.
I have these two tables (see screenshot) and I need to create a 3rd table with columns FaultName and FaultCount. It is simple if the tables are SQL tables as you can access the columns. But ignition tables doesn’t have column selection. And appendDataset not working as well. Any ideas? Can it be achieved in ignition at all?

Thank you in advance.

Faults tables
Faults tables473×616 6.29 KB

6

Off the top of my head.
Will be better with list comprehension but this will get you started.

ds1 = system.dataset.toPyDataSet(event.source.parent.getComponent('Power Table').data)
ds2 = system.dataset.toPyDataSet(event.source.parent.getComponent('Power Table 1').data)
tbl = event.source.parent.getComponent('Power Table 2')
ds3 = []
hdr=['faultID','FaultName','FaultCount']
for x in ds1:
	for f in ds2:
		if f[0]==x[0]:
			ds3.append([x[0],x[1],f[1]])
tbl.data = system.dataset.toDataSet(hdr,ds3)
7

It would be more efficient to make a dictionary out of the first dataset for lookups.

headers1 = ['FaultID', 'FaultName']
data1    = [[1, 'Return Temp Alarm Low Low'],
            [2, 'Return Alarm Temp High High'],
            [3, 'STOP Command'],
            [4, 'Caustic Tank Level Low Low'],
            [5, 'Acid Tank Level Low Low']
           ]
headers2 = ['FaultID', 'FaultCount']
data2    = [[3, 543],
            [2, 210],
            [4, 98],
            [1, 76],
            [5, 5]
           ]
           
ds1 = system.dataset.toPyDataSet(system.dataset.toDataSet(headers1, data1))
ds2 = system.dataset.toPyDataSet(system.dataset.toDataSet(headers2, data2))
#print '-- ds1 --'          
#util.printDataSet(ds1)
#print '-- ds2 --'
#util.printDataSet(ds2)

lookup = {row['FaultID']:row['FaultName'] for row in ds1}

headersOut = ['FaultID', 'FaultName', 'FaultCount']
dataOut = [[row['FaultID'], lookup[row['FaultID']], row['FaultCount']] for row in ds2]

dsOut = system.dataset.toDataSet(headersOut, dataOut)
#print '-- dsOut --'
#util.printDataSet(dsOut)

Output:

-- ds1 --
row | FaultID | FaultName                  
-------------------------------------------
0   | 1       | Return Temp Alarm Low Low  
1   | 2       | Return Alarm Temp High High
2   | 3       | STOP Command               
3   | 4       | Caustic Tank Level Low Low 
4   | 5       | Acid Tank Level Low Low    
-- ds2 --
row | FaultID | FaultCount
--------------------------
0   | 3       | 543       
1   | 2       | 210       
2   | 4       | 98        
3   | 1       | 76        
4   | 5       | 5         
-- dsOut --
row | FaultID | FaultName                   | FaultCount
--------------------------------------------------------
0   | 3       | STOP Command                | 543       
1   | 2       | Return Alarm Temp High High | 210       
2   | 4       | Caustic Tank Level Low Low  | 98        
3   | 1       | Return Temp Alarm Low Low   | 76        
4   | 5       | Acid Tank Level Low Low     | 5
1 Like