JMP Meets SQL Using Query Builder for JMP

JMP® Meets SQL: Using Query Builder for JMP Data Tables Eric Hill, JMP Abstract • JMP 13 includes a Query Builder for JMP data tables • Join up to 64 tables • Filter data interactively and create prompts • Query() JSL function lets you write SQL to manipulate JMP data tables Example – 4 JMP Tables Table 4: 51 rows, key“SSN” “State” Name” 2: BC_Social. Security –– 40 rows, key “Student 1: Big Class – 40 key “name” 3: Avg_SAT_By_State SAT_Scores – Rows, 126, 480 key Next

Adding Tables to the Query 1. Open the. JMP data tables 2. Query Builder 3. Launch Add Tables and Create Joins Auto-Join did not succeed Join tables manually Done adding tables Build the query Next

Configuring the Query 8. 10. Save Create and athe Run prompted query 6. Customize columns 5. Select columns to include 9. Examine generated SQL 7. Add Filters Set all filters to “prompt” Examine the generated SQL Add additional filters if desired Save and Close the prompting query Save the query to a file before you run it Run the query Next

Running the Prompting Query 11. Run the query from the Home Window Next

Executing SQL from JSL Find students who performed better than their state average in both Verbal and Math Contact Info: Eric Hill eric. hill@jmp. com Source of data for average SAT scores by state: http: //blog. prepscholar. com/average-sat-scores-by-state-most-recent Restart