Names Default To Here (1); New SQL Query( Version( 130 ), Connection( "ODBC:DSN=SQLServer SQBTest;UID=uid;PWD=pw;APP=JMP;WSID=machineid;DATABASE=mydatabase;" ), QueryName( "worldbank_2018" ), Select( Column( "Country Name", "t1" ), Column( "Country Code", "t1" ), Column( "Indicator Name", "t1" ), Column( "Indicator Code", "t1" ), Column( "2010", "t1" ), Column( "2011", "t1" ), Column( "2012", "t1" ), Column( "2013", "t1" ), Column( "2014", "t1" ), Column( "2015", "t1" ), Column( "2016", "t1" ), Column( "2017", "t1" ), Column( "2018", "t1" ) ), From( Table( "worldbank_2018", Schema( "SQBTest" ), Alias( "t1" ) ) ), Where( In List( Column( "Country Code", "t1" ), {"CZE", "DEU", "DNK", "FRA", "GBR", "POL", "SWE"}, UI( Match Column Values( "EU", "C:\Discovery Demo\Copenhagen\EU.jmp", "CountryCode", SelectedRows( 1 ), Base( "Categorical" ) ) ) ) & Contains( Column( "Indicator Code", "t1" ), "eg.", UI( Contains( Base( "Categorical" ) ) ) ) & In List( Column( "Indicator Name", "t1" ), {"Electricity production from oil, gas and coal sources (% of total)"}, UI( SelectListFilter( ListBox, Base( "Categorical", Conditional ) ) ) ) ), Order By( Column( "Indicator Code", "t1", Order( "Ascending" ) ) ) ) << Run Foreground(); gbElectric = Graph Builder( Size( 534, 490 ), Show Control Panel( 0 ), Variables( Group X( :Indicator Name ), Color( :Name( "2015" ) ), Shape( :Country Name ) ), Elements( Map Shapes( Legend( 4 ) ) ) ); wait(3.0); Names Default To Here( 1 ); New Namespace("openaq"); openaq:Get Countries = Function({codes = Empty(), visible = 1}, {Default Local}, request = New HTTP Request( URL("https://api.openaq.org/v1/countries"), Method("Get") ); json = request << Send(); dt = Empty(); if(!Is Empty(json), json_jsl = Parse JSON(json); if(Contains(json_jsl, "results"), countries = JSON To Data Table(As JSON Expr(json_jsl["results"]), Invisible(1)); countries:name << Set Selected(1); countries << Move Selected Columns(To First); countries:name << Set Selected(0); countries:locations << Set Selected(1); countries << Move Selected Columns(After(countries:code)); countries:locations << Set Selected(0); count = N Items(codes); if(count == 0, dt = countries; , dt = New Table("Open AQ Platform API", "invisible"); dt << New Column("name", Character, Nominal); dt << New Column("code", Character, Nominal); dt << Add Rows(count); data = Associative Array(countries:code << Get Values(), countries:name << Get Values()); for(i = 1, i <= count, i++, dt:code[i] = codes[i]; dt:name[i] = data[codes[i]]; ); Close(countries, "NoSave"); ); dt << Set Name("Open AQ Platform API"); if(visible, dt << Show Window(1); ); ); ); dt; ); openaq:Get Latest = Function({name, code}, {Default Local}, dt = Empty(); if(code != "", query = [=>]; query["country"] = code; query["limit"] = 200; request = New HTTP Request ( URL("https://api.openaq.org/v1/latest"), Method("Get"), Query String(query) ); json = request << Send(); if(!Is Empty(json), json_jsl = Parse JSON(json); if(Contains(json_jsl, "results"), dt = JSON To Data Table(As JSON Expr(json_jsl["results"])); //allowed values pm25, pm10, so2, no2, o3, co, bc pollutants = Associative Array( {"pm25", "pm10", "so2", "no2", "o3", "co", "bc"}, {"Particulate matter 2.5 micrometers or less in diameter", "Particulate matter 10 micrometers or less in diameter", "Sulfur dioxide", "Nitrogen dioxide", "Ozone", "Carbon monoxide", "Black carbon" } ); //fix the colum names dt:coordinates.latitude << Set Name("latitude"); dt:coordinates.longitude << Set Name("longitude"); dt:measurements.averagingPeriod.unit << Set Name("averagingPeriod.unit"); dt:measurements.averagingPeriod.value << Set Name("averagingPeriod.value"); dt:measurements.lastUpdated << Set Name("lastUpdated"); dt:measurements.parameter << Set Name("parameter"); dt:measurements.sourceName << Set Name("sourceName"); dt:measurements.unit << Set Name("unit"); dt:measurements.value << Set Name("value"); dt << New Column("pollutant", "Character") << Set Hidden(1); dt << New Column("measurement", "Character") << Set Hidden(1); dt << Select Where(Is Missing(dt:latitude)); dt << Delete Rows; //fix the dates, make distance in km, measurement label For(i = 1, i <= N Rows(dt), i++, timestamp = dt:lastUpdated[i]; if(EndsWith(timestamp, "Z"), timestamp = Substr(timestamp, 0, Length(timestamp) - 1); ); dt:lastUpdated[i] = timestamp; dt:distance[i] = dt:distance[i]/1000; dt:measurement[i] = Char(dt:value[i]) || " " || dt:unit[i]; If(Contains(pollutants, dt:parameter[i]), dt:pollutant[i] = pollutants[dt:parameter[i]]; , dt:pollutant[i] = dt:parameter[i]; ); ); dt:lastUpdated << Set Data Type("Numeric") << Set Modeling Type("Continuous") << Format( "Locale Date Time h:m:s", 23, 0 ) << Input Format( "yyyy-mm-ddThh:mm:ss", 0 ); dt:distance << Format( "Fixed Dec", 12, 2 ) << Set Name("distance (km)"); dt:latitude << Format("Latitude DMS", "PUNDIR", 15, 0 ); dt:longitude << Format("Longitude DMS", "PUNDIR", 15, 0 ); dt << New Column("name", "Character"); dt:name << Set Values(Repeat({name}, N Rows(dt))); dt:name << Set Selected(1); dt << Move Selected Columns(toFirst); dt:name << Set Selected(0); dt << New Column("code", "Character"); dt:code << Set Values(Repeat({code}, N Rows(dt))); dt:code << Set Selected(1); dt << Move Selected Columns(After(dt:name)); dt:code << Set Selected(0); //set the labeled columns dt << Set Label Columns(:location, :city, :name, :pollutant, :measurement, :lastUpdated); dt << Set Name("Air Quality (" || name || ")"); ); ); ); dt; ); openaq:Combine Countries = Function({table=CurrentDatatable(), selectAll = 0}, {Default Local}, names = {}; codes = {}; if(selectAll, table << Select All Rows(); ); selection = table << Get Selected Rows(); for(i = 1, i <= N Items(selection), i++, name = table:name[selection[i]]; InsertInto(names, name); code = table:code[selection[i]]; InsertInto(codes, code); ); dt = Empty(); count = N Items(codes); for(i = 1, i <= count, i++, t = openaq:Get Latest(names[i], codes[i]); if(i == 1, dt = t; , dt << Concatenate(t, Append to first table); Close(t, NoSave); ); ); if(count > 0, dt << Set Name("Combined Air Quality for Countries"); scripts = dt << Get Table Script Names(); if(NItems(scripts), dt << Delete Scripts(scripts); ); ); if(!IsEmpty(dt), dt << ShowWindow(1); ); dt; dt << Save("c:\Discovery Demo\Copenhagen\Combined Air Quality.jmp") ); //initial countries codes = {"DK", "SE", "GB", "FR", "DE", "CZ", "PL"}; visible = 0; //get the table, but invisible dt = openaq:Get Countries(codes, visible); if(!Is Empty(dt), dt:name << Set Property("Event Handler", Event Handler( Click( JSL Quote( Function( {thisTable, thisColumn, iRow}, // 3 parameters {Default Local }, openaq:Get Latest(thisTable:name[iRow], thisTable:code[iRow]); ); ); ), Tip( JSL Quote( Function( {thisTable, thisColumn, iRow}, // 3 parameters {Default Local }, "Show air quality data for " || thisTable:thisColumn[iRow]; ); ); ); ); ); dt << New Script("Combined Air Quality for Countries", openaq:Combine Countries(CurrentDataTable()); ); ); //this stacks them upfront if(!visible, selectAll = 1; //use all the data in the table countries = openaq:Combine Countries(CurrentDataTable(), selectAll); //close dt, we only want the stacked table Close(dt, "NoSave"); ); wait(3.0); //openaq << Delete Namespace; qdt1 = New SQL Query( Version( 130 ), Connection( "JMP" ), JMP Tables( ["Combined Air Quality" => "\c:\Discovery Demo\Copenhagen\Combined Air Quality.jmp"] ), QueryName( "SQLQuery1" ), Select( Column( "name", "t1" ), Column( "code", "t1" ), Column( "city", "t1" ), Column( "latitude", "t1", Numeric Format( "Latitude DMS", "0", "NO", "" ) ), Column( "longitude", "t1", Numeric Format( "Longitude DMS", "0", "NO", "" ) ), Column( "country", "t1" ), Column( "distance (km)", "t1", Numeric Format( "Fixed Dec", "2", "NO", "" ) ), Column( "location", "t1" ), Column( "averagingPeriod.unit", "t1" ), Column( "averagingPeriod.value", "t1" ), Column( "lastUpdated", "t1", Numeric Format( "Locale Date Time h:m:s", "0", "NO", "" ) ), Column( "parameter", "t1" ), Column( "sourceName", "t1" ), Column( "unit", "t1" ), Column( "value", "t1" ), Column( "pollutant", "t1" ), Column( "measurement", "t1" ) ), From( Table( "Combined Air Quality", Alias( "t1" ) ) ), Where( GE( Column( "latitude", "t1", Numeric Format( "Latitude DMS", "0", "NO", "" ) ), 42, UI( Comparison( Base( "Continuous" ) ) ) ) & In List( Column( "pollutant", "t1" ), {"Particulate matter 2.5 micrometers or less in diameter"}, UI( SelectListFilter( ListBox, Base( "Categorical" ) ) ) ) ) ) << Run Foreground(); wait(1.0); qdt2 = New SQL Query( Version( 130 ), Connection( "JMP" ), JMP Tables( ["Combined Air Quality" => "\c:\Discovery Demo\Copenhagen\Combined Air Quality.jmp"] ), QueryName( "SQLQuery1" ), Select( Column( "name", "t1" ), Column( "code", "t1" ), Column( "city", "t1" ), Column( "latitude", "t1", Numeric Format( "Latitude DMS", "0", "NO", "" ) ), Column( "longitude", "t1", Numeric Format( "Longitude DMS", "0", "NO", "" ) ), Column( "country", "t1" ), Column( "distance (km)", "t1", Numeric Format( "Fixed Dec", "2", "NO", "" ) ), Column( "location", "t1" ), Column( "averagingPeriod.unit", "t1" ), Column( "averagingPeriod.value", "t1" ), Column( "lastUpdated", "t1", Numeric Format( "Locale Date Time h:m:s", "0", "NO", "" ) ), Column( "parameter", "t1" ), Column( "sourceName", "t1" ), Column( "unit", "t1" ), Column( "value", "t1" ), Column( "pollutant", "t1" ), Column( "measurement", "t1" ) ), From( Table( "Combined Air Quality", Alias( "t1" ) ) ), Where( GE( Column( "latitude", "t1", Numeric Format( "Latitude DMS", "0", "NO", "" ) ), 42, UI( Comparison( Base( "Continuous" ) ) ) ) & In List( Column( "pollutant", "t1" ), {"Carbon monoxide"}, UI( SelectListFilter( ListBox, Base( "Categorical" ) ) ) ) ) ) << Run Foreground(); wait(1.0); current data table(qdt1); gbPart = Graph Builder( Size( 534, 490 ), Show Control Panel( 0 ), Variables( Group X( :pollutant ), Color( :value ), Shape( :country ) ), Elements( Map Shapes( Legend( 8 ) ) ) ); current data table(qdt2); gbCO = Graph Builder( Size( 534, 490 ), Show Control Panel( 0 ), Variables( Group X( :pollutant ), Color( :value ), Shape( :country ) ), Elements( Map Shapes( Legend( 8 ) ) ) ); app = JMP App(); app << Set Name( "Dashboard of Air Quality" ); app << Combine Windows( {gbElectric << Report, gbPart << Report, gbCO << Report} ); (app << Get Modules)[1] << Set Window Title( "Dashboard of Air Quality" ); appOutput = app << Run; winList = app << Get Windows(); dashWin = winList[1]; dashWin << Save PDF("c:\Discovery Demo\Copenhagen\DashboardOfPollutants.pdf"); Preferences( JMP Server URL("https://public.jmp.com") ); webreport = New Web Report(); webreport << Add Report( app ); use_data = "true"; url = webreport << Publish( URL("https://public.jmp.com"), Username("somebody@company.com"),Public(1), Password("mypassword"), Publish Data(use_data) ); If( !Is Empty( url ), Web( url ) ); Quit("No Save");