# Data for boxplot **URL:** \ **Category:** Ignition\ **Created:** [November 16, 2016, 3:30pm UTC](https://forum.inductiveautomation.com/t/data-for-boxplot/12365 "2016-11-16T15:30:33Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![the\_msp](https://avatars.discourse-cdn.com/v4/letter/t/b38774/32.png) [@the\_msp](https://forum.inductiveautomation.com/u/the_msp)\ **Post date:** [November 16, 2016, 3:30pm UTC](https://forum.inductiveautomation.com/t/data-for-boxplot/12365/1 "2016-11-16T15:30:33Z")
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? ---
**Author:** ![the\_msp](https://avatars.discourse-cdn.com/v4/letter/t/b38774/32.png) [@the\_msp](https://forum.inductiveautomation.com/u/the_msp)\ **Post date:** [November 22, 2016, 1:34pm UTC](https://forum.inductiveautomation.com/t/data-for-boxplot/12365/2 "2016-11-22T13:34:43Z")
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? ---
**Author:** ![jpark](https://avatars.discourse-cdn.com/v4/letter/j/a88e57/32.png) [@jpark](https://forum.inductiveautomation.com/u/jpark)\ **Post date:** [November 22, 2016, 3:43pm UTC](https://forum.inductiveautomation.com/t/data-for-boxplot/12365/3 "2016-11-22T15:43:01Z")
[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] ---
**Author:** ![the\_msp](https://avatars.discourse-cdn.com/v4/letter/t/b38774/32.png) [@the\_msp](https://forum.inductiveautomation.com/u/the_msp)\ **Post date:** [November 25, 2016, 8:36am UTC](https://forum.inductiveautomation.com/t/data-for-boxplot/12365/4 "2016-11-25T08:36:15Z")
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]