I want to get data into a box plot and have tried a few methods with limited success.
There are 4 stations (split into two stations with two substations) and each logs into a DB (Sql server) 8 parameters. I want a box plot showing 4 plots (for each station) and a drop down to select the parameter to compare.
I started with one parameter and one station/substation with this SQL query direct in the box plot data source:
select * from
(select t_weld as S3_SS0 from laserweld where station = 3 and substation = 0 and datetime > DateAdd(HOUR,-1,getdate())) A,
(select t_weld as S3_SS1 from laserweld where station = 3 and substation = 1 and datetime > DateAdd(HOUR,-1,getdate())) B
However for some reason one column will return with proper values, and the other will just repeat one value in each row (does this for 4 stations as well as 1).
So next I thought of scripting a bit, kind of doing double work but had a go at:
[code]system.db.runPrepUpdate(“delete from boxplot”)
s3_ss0 = system.db.runQuery(“select t_weld from laserweld where station = 3 and substation = 0 and datetime > DateAdd(HOUR,-1,getdate())”)
print s3_ss0
for row in s3_ss0:
data = row[0]
print data
system.db.runPrepUpdate(“insert into boxplot (s3_ss0) VALUES (?)”, [data])
s3_ss1 = system.db.runQuery(“select t_weld from laserweld where station = 3 and substation = 1 and datetime > DateAdd(HOUR,-1,getdate())”)
print s3_ss1
for row in s3_ss1:
data2 = row[0]
print data2
system.db.runPrepUpdate(“insert into boxplot (s3_ss1) VALUES (?)”, [data2])[/code]
However this won’t work as the results aren’t on the same row therefore when there is data in the 1st column a null field is in the 2nd which errors out the box plot.
[code]s3_ss0 = system.db.runQuery(“select t_weld from laserweld where station = 3 and substation = 0 and datetime > DateAdd(HOUR,-12,getdate())”)
s3_ss1 = system.db.runQuery(“select t_weld from laserweld where station = 3 and substation = 1 and datetime > DateAdd(HOUR,-12,getdate())”) #s4_ss0 = system.db.runQuery(“select t_weld from laserweld where station = 4 and substation = 0 and datetime > DateAdd(HOUR,-12,getdate())”)
s4_ss1 = system.db.runQuery(“select t_weld from laserweld where station = 4 and substation = 1 and datetime > DateAdd(HOUR,-12,getdate())”)
event.source.parent.getComponent(‘Box and Whisker Chart’).data = xybDataSet[/code]
Next I would like a drop down with “s_weld, t_weld” etc injecting into my sql select, but this isn’t possible as a column can’t be indirect. Is there a work around?
[code]dropDownValue = “t_weld” # Change this to the dropdown value
s3_ss0 = system.db.runQuery(“select %s from laserweld where station = 3 and substation = 0 and datetime > DateAdd(HOUR,-12,getdate())”%dropDownValue)
s3_ss1 = system.db.runQuery(“select %s from laserweld where station = 3 and substation = 1 and datetime > DateAdd(HOUR,-12,getdate())”%dropDownValue) #s4_ss0 = system.db.runQuery(“select %s from laserweld where station = 4 and substation = 0 and datetime > DateAdd(HOUR,-12,getdate())”%dropDownValue)
s4_ss1 = system.db.runQuery(“select %s from laserweld where station = 4 and substation = 1 and datetime > DateAdd(HOUR,-12,getdate())”%dropDownValue)
s3_ss0 = system.db.runPrepQuery(“select %s from laserweld where station = 3 and substation = 0 and datetime between ? and ?” %dropDownValue, [start_date, end_date])
s3_ss1 = system.db.runPrepQuery(“select %s from laserweld where station = 3 and substation = 1 and datetime between ? and ?” %dropDownValue, [start_date, end_date]) #s4_ss0 = system.db.runQuery(“select %s from laserweld where station = 4 and substation = 0 and datetime > DateAdd(HOUR,-12,getdate())”%dropDownValue)
s4_ss1 = system.db.runPrepQuery(“select %s from laserweld where station = 4 and substation = 1 and datetime between ? and ?” %dropDownValue, [start_date, end_date])