Using JMP • Reshape Data • Query and Join Data Tables with JMP Query Builder

For the latest version of JMP Help, visit JMP.com/help.

Previous
 • 
Next
Query and Join Data Tables with JMP Query Builder
The JMP Query Builder option in the Tables menu enables you to query data tables and save selected data into a new data table. This feature is similar to using the Join command but lets you perform queries before saving the data. For example, you can query SAT data and save only data for 2004 test scores in a data table. You can also include a prompt that lets the user run the query and choose a subset of the 2004 test scores.
1.
Select Help > Sample Data Library and open SAT.jmp and SATByYear.jmp.
2.
Display SATByYear.jmp.
3.
Select Tables > JMP Query Builder.
The current data table, SATByYear.jmp, is selected as the Primary table.
4.
Select SAT.jmp in the Available Tables list and click Secondary.
5.
Select SAT.jmp next to the Secondary button and view the Columns tab. The Join column shows that two columns have the same name and were joined.
Figure 6.33 Joined Columns
6.
Click Build Query.
Add Columns
1.
In the Available Columns list, select t1.State, t1.%Taking (2004), t2.2004 Verbal, and t2.2004 Math.
2.
Click Add.
The columns are added to the Included Columns tab.
3.
Select Distinct rows only to avoid saving duplicate rows.
4.
On the Query Preview tab, make sure that Update preview automatically is selected so that you can see the selected columns.
Add Filters
1.
In the Included Columns list, select t2.2004 Verbal and t2.2004 Math, and then click Add Selected Items to Filter .
The columns are added to the Filters list.
Figure 6.34 Selected Filters
2.
Click the red triangle next to each filter and select Prompt on Run. Accept the default prompt message.
Run the Query
1.
Click Run Query.
You are prompted to indicate which math and verbal scores to save in the data table. To save all data, do not change the values in the prompt window.
2.
Click OK to create the data table.
Figure 6.35 Queried Data
See Build SQL Queries in Query Builder in Import Your Data for more information about features that also appear in Query Builder for databases.
Tips: 
•
To join data from different sources (for example, a database and Microsoft Excel), use Query Builder to import the database data into a data table; import the Excel data into a data table; and use JMP Query Builder in the Tables menu to query and join the tables.
•
When you open a query, data tables in the query open as hidden files that you can open from the JMP Home Window. You can also open the hidden files from the Table panel when you build the query.
•
If you try to query a Microsoft Excel file that has more than 255 columns, only 255 columns are imported. Instead, select File > Open to open the Excel file in the Excel Import Wizard, and save the file as a data table.

Previous
 • 
Next
Help created on 3/19/2020