Hello,
I need help with how to select a value from another column if a value in another column is great than some value. for example
Machine P1 P2
1 150 145
2 220 275
3 201 186
how do I write a script to return the machine number if value in P2 is greater than 270?
Thank you for your help.
Regards.
Ignition version? If 8+, Vision or Perspective?
Python table, you mean Power Table etc in vision? Or data in SQL DB? Which SQL?
Until you answer the above, the below is a guess.
Create a tag type SQL, pseudo-code query:
Select machineNum from db.table
where P2 > '270'
Output as per your example dataset will be ‘2’
Of course, that is a hardcoded value of 270, make it a variable and link it to your input parameter
Good Morning,
Thank you for your response.
.the way I have it set up right now, is I created an expression tag and have a SQL query pulling the data. and I am using Table to show me the data. however next I want to notify me when a value gets above 270.
so far I only have
if system.tag.read(Notification/Parameters/“Send Email”).value == True
table = event.source.parent.getComponent(‘Table’)
data = system.dataset.toPyDataSet(Table.data)
Next, i would like to look at column 2 and if any of the values are above 270 i would like to get the machine number from column 0
Thank you
Gijo
You didn’t answer all my questions, please do.
Use the code button (</> symbol) when pasting Jython/SQL/error code here.
i apologize.
i am using ignition 7.9.10. i am not sure what vision or perspective means. i am getting the data from MYSQL and then into a table. the table works, it is showing the data that is being pulled through SQL.
if system.tag.read("Email Notifications/Parameters/Send Email when Primary Pall filters are getting OLD").value == True
table = event.source.parent.getComponent('Table')
data = system.dataset.toPyDataSet(Table.data) # this converts the dataset into a py dataset
Perspective only applies to 8.0.0 and above.
I think you clicked the wrong button, because your formatting should be preserved (tabs and white space, this is important in Jython):
if system.tag.read("Email Notifications/Parameters/Send Email when Primary Pall filters are getting OLD").value == True
table = event.source.parent.getComponent('Table')
data = system.dataset.toPyDataSet(Table.data) # this converts the dataset into a py dataset
Python (Jython) if statements need a colon then a tab on the next line, see example below. Go to Tools/script console and paste in and hit execute
for x in range(0,9):
if x > 5:
print x
else:
print "<5"
the way I have it set up right now, is I created an expression tag and have a SQL query pulling the data
I think you need to spend a bit of time on Inductive University, make sure to change the version to 7.9
I would have your table get the data from MySQL. If all you want to show then is one row, write the SQL query to only return this.
Or if you want the table to show all data, then select *
If you want the machine number shown, for example as a numeric label, create a numeric label and bind this to a SQL tag that has something similar to my first reply pseudo-code.
3rd line should be:
WHERE P2 > 270
(no quotes around the integer value)
If you did want to process the table though instead of running another query:
if system.tag.read("Email Notifications/Parameters/Send Email when Primary Pall filters are getting OLD").value == True
table = event.source.parent.getComponent('Table')
data = system.dataset.toPyDataSet(Table.data)
P1_Old = []
for row in data if row['P2'] > 270:
P1_Old.append('Machine %s P1 %s [%s]' % (row['Machine'], row['P1'], row['P2'])
badFiltersCSV = ', '.join(P1_Old)
# do something with the above...
Been a long day 
AHEM:
In an expression binding:
view("SELECT machineNum Where P2>270", {Root Container.path.to.dataset.property})
From my free Simulation Aids module. (:
Using a list comprehension:
if system.tag.read("Email Notifications/Parameters/Send Email when Primary Pall filters are getting OLD").value == True
table = event.source.parent.getComponent('Table')
dataIn = system.dataset.toPyDataSet(Table.data)
headers = dataIn.getColumnNames()
dataOut = [list(row) for row in dataIn if row['P2'] > 270]
datasetOut = system.dataset.toDataSet(headers, dataOut)
Good Morning Jordan,
So when selecting the row. i need to use the row name and not P2 correct?
Thank you
Gijo
Hi Gijo,
The example I gave will make a new ‘filtered’ dataset containing rows where P2 > 270. Not sure what you are meaning by selecting a row.
In Jordan’s code, this line:
dataOut = [list(row) for row in dataIn if row['P2'] > 270]
is equivelant to:
dataOut = []
for row in dataIn:
if row['P2'] > 270:
dataOut.append(list(row))
Since you have converted your dataIn dataset to a PyDataset you can use this syntax to reference the value in the column of a row by name. So, “row[‘P2’]” says get the value of the current row at column ‘P2’
Hello Jordan,
if system.tag.read("Email Notifications/Parameters/Send Email when Primary Pall filters are getting OLD").value == True
table = event.source.parent.getComponent('Table')
data = system.dataset.toPyDataSet(Table.data) # this converts the dataset into a py dataset
headers = dataIn.getColumnNames()
dataOut = [list(row) for row in dataIn if row['Pri CMD'] > 260]
datasetOut = system.dataset.toDataSet(headers, dataOut)
else:
loggerGatewayScripts = system.util.getLogger("Symation.Email_Logger")
sSubject = "Primary Pall Filter getting clogged"
sBody = "please schedule to repalce these Primary Pall filters. " + datasetOut
sSubject=str(system.tag.read("Tower Config/Tower Number").value) + " "+sSubject
sBody="Tower " + str(system.tag.read("Tower Config/Tower Number").value)+ "\n" + sBody
sRecipients = ["gvarghese@ofsoptics.com"]
loggerGatewayScripts.infof("Sending Line Idle Email: %s",sSubject)
system.net.sendEmail("smtp.corp.ofsoptics.com:25","gvarghese@ofsoptics.com",sSubject,sBody,0,sRecipients)
except:
loggerGatewayScripts = system.util.getLogger("Symation.Email_Logger")
loggerGatewayScripts.infof("Error sending Clogged Primary Pall Filter email")
Where is this script located? How is it triggered?
Sorry, idk what i answered
i have a memory tag active. and this should execute when that memory tag is activated.
There is an error in the script as it is in your post. If this script is in a tag’s valueChanged event as you have it posted, then it is probably not executing. Are you seeing any messages in your logger? You can look at the Tag Diagnostics to see if the script is throwing an exception or not.
You need to delete the else: or you need to provide something that should be executed after the else. Also, you have an except, but no ‘Try’, is this the entire script?
I don’t think that you will get what you expect in the body with just concatenating datasetOut to the body. You will need to loop through the filtered dataset and concatenate the information that way.
Something like the following is what I would do:
try:
if system.tag.read("Email Notifications/Parameters/Send Email when Primary Pall filters are getting OLD").value == True:
table = event.source.parent.getComponent('Table')
data = system.dataset.toPyDataSet(Table.data) # this converts the dataset into a py dataset
headers = dataIn.getColumnNames()
dataOut = [list(row) for row in dataIn if row['Pri CMD'] > 260]
datasetOut = system.dataset.toDataSet(headers, dataOut)
loggerGatewayScripts = system.util.getLogger("Symation.Email_Logger")
sSubject = "Primary Pall Filter getting clogged"
sBody = "please schedule to repalce these Primary Pall filter"
for row in datasetOut:
sBody += "\n Tower: %i, Pri Cmd: %i" % (row['Tower'],row['Pri CMD'])
sRecipients = ["gvarghese@ofsoptics.com"]
loggerGatewayScripts.infof("Sending Line Idle Email: %s",sSubject)
system.net.sendEmail("smtp.corp.ofsoptics.com:25","gvarghese@ofsoptics.com",sSubject,sBody,0,sRecipients)
except:
loggerGatewayScripts = system.util.getLogger("Symation.Email_Logger")
loggerGatewayScripts.infof("Error sending Clogged Primary Pall Filter email")So i had an error in the script i was missing “:” at the end of my IF statement. I got that fixed. and have no more errors. i updated the script, i am now seeing stuff updating on the logger. but for some reason, the script do not get past the Initialchange: line.
loggerGatewayScripts = system.util.getLogger("Symation.Email_Logger")
loggerGatewayScripts.infof("Primary Pall Filter Script Executed!")
if not initialChange:
loggerGatewayScripts.infof("Primary Pall Filter Script Executed!")
try:
if system.tag.read("Email Notifications/Parameters/Send Email when Primary Pall filters are getting OLD").value == True:
table = event.source.parent.getComponent('Table')
loggerGatewayScripts.infof("Primary Pall Filter Script inside first if statement")
data = system.dataset.toPyDataSet(Table.data) # this converts the dataset into a py dataset
headers = dataIn.getColumnNames()
dataOut = [list(row) for row in dataIn if row['Pri CMD'] > 260]
datasetOut = system.dataset.toDataSet(headers, dataOut)
if len(dataOut) > 0:
sSubject = "Primary Pall Filter getting clogged"
sBody = "please schedule to repalce these Primary Pall filters. " + datasetOut
sSubject=str(system.tag.read("Tower Config/Tower Number").value) + " "+sSubject
sBody="Tower " + str(system.tag.read("Tower Config/Tower Number").value)+ "\n" + sBody
sRecipients = ["gvarghese@ofsoptics.com"]
loggerGatewayScripts.infof("Sending Line Idle Email: %s",sSubject)
system.net.sendEmail("smtp.corp.ofsoptics.com:25","gvarghese@ofsoptics.com",sSubject,sBody,0,sRecipients)
except:
loggerGatewayScripts.infof("Error sending Clogged Primary Pall Filter email")
Should be a lowercase 't' in 'Table' based on your variable name 'table' two lines above.
Also, this script will never work in a Tag Event Script, or any Gateway Script. You cannot access components (such as 'Table') from a Tag/Gateway script (gateway vs client scope). You will need to setup a different means to pass your data into this script (separate dataset tag, modify script to retrieve source data, etc).