Data for boxplot

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.

Any suggestions?

OK, getting there;

[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())”)

xDataSet = s3_ss0
yDataSet = s3_ss1
#zDataSet = s4_ss0
bDataSet = s4_ss1

xs = [row[0] for row in xDataSet]
ys = [row[0] for row in yDataSet]
#zs = [row[0] for row in zDataSet]
bs = [row[0] for row in bDataSet]

rows = zip(xs, ys, bs)
xybDataSet = system.dataset.toDataSet([‘S3_SS0’, ‘S3_SS1’, ‘S4_SS1’], rows)

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)

xDataSet = s3_ss0
yDataSet = s3_ss1
#zDataSet = s4_ss0
bDataSet = s4_ss1

xs = [row[0] for row in xDataSet]
ys = [row[0] for row in yDataSet]
#zs = [row[0] for row in zDataSet]
bs = [row[0] for row in bDataSet]

rows = zip(xs, ys, bs)
xybDataSet = system.dataset.toDataSet([‘S3_SS0’, ‘S3_SS1’, ‘S4_SS1’], rows)

event.source.parent.getComponent(‘Box and Whisker Chart’).data = xybDataSet[/code]

Thanks, Jpark!

Ended up changing to prep for a date range component.

[code]dropDownValue = event.source.parent.getComponent(‘Dropdown’).selectedStringValue
start_date = event.source.parent.getComponent(‘Date Range’).startDate
end_date = event.source.parent.getComponent(‘Date Range’).endDate

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

xDataSet = s3_ss0
yDataSet = s3_ss1
#zDataSet = s4_ss0
bDataSet = s4_ss1

xs = [row[0] for row in xDataSet]
ys = [row[0] for row in yDataSet]
#zs = [row[0] for row in zDataSet]
bs = [row[0] for row in bDataSet]

rows = zip(xs, ys, bs)
xybDataSet = system.dataset.toDataSet([‘S3_SS0’, ‘S3_SS1’, ‘S4_SS1’], rows)

event.source.parent.getComponent(‘Box and Whisker Chart’).data = xybDataSet[/code]