Query Builder (2022-US-EPO-1160)

Canh Khong, Samson Rope Technologies, Samson Rope Technologies

Extracting and combining data from multiple, disconnected databases was one of the biggest challenges encountered at Samson Rope Technologies before our investment in JMP Statistical Software. With the incredibly useful JMP Query Builder, this challenge has been economically addressed.

This presentation describes the way Samson uses the JMP Query Builder to easily extract desired records and fields from a single database (leaving behind the fields that are not of interest) and, more importantly, extract desired records and fields from multiple databases and combine them into a single “master table” file with all required data in one place, ready for rigorous statistical analysis, typically by an R&D Engineer, Quality Engineer or Manufacturing Engineer working on a continuous improvement project.

One of the best JMP features is that JMP creates scripts behind the scenes and if the scripts are properly managed, there is no need to spend time repeating the previous steps to create or update the “master table”. And, the best part of the JMP Query Builder is that we can create complex JMP scripts just by simple “copy and paste” and combination of the scripts JMP creates for each previous action. No background in scripting or coding is needed to create a JMP script!

This poster will be highly graphical in nature, drawing the attention of the viewer to the practical, useful benefits of the JMP Query Builder. Any organization with multiple databases containing important information should benefit from this poster.

Hello. My name is Canh Khong.

I work at Samson Rope Technology in Ferndale, Washington State.

Today, I am talking about how to build a Query Builder using JMP software.

Let's start with a working table that has a few columns

such as date, operator i nitial, job number, actual measurements.

The working table is just for manual data collection.

To analyze, we may need to add more information from other tables.

How can I do that? The answer is using a Query Builder.

Query Builder can extract desired columns from other tables

and join these columns to the working table that I call the Master Table.

The Master Table has all of the information needed for analysis.

Here are a few steps to build a Query Builder

to join the working table to another table.

These steps are as simple as point, click, drag and drop.

Now, the Master Table is ready for data analysis.

However, if needed, a new column with a formula can be manually created

adding to the Master Table,

and JMP will automatically create the corresponding script of a new column.

Copy and paste the script to the Post Query Script.

If this is done, there is no need to spend time to recreate the new column.

The new column will be updated and shown every time the Query Builder runs.

If desired, the report can be manually created.

And JMP will create the corresponding script.

Copy and paste the script to the Post Query Script.

If this is done, there's no need to spend time to recreate the report.

The report will be automatically updated

and shown every time the Query Builder runs.

If desired, a chart can be manually created

and JMP will create the corresponding script.

Copy and paste the script to the Post Query Script.

If this is done, there is no need to spend time to recreate the chart.

The chart will be automatically updated

and shown every time the Query Builder runs.

One click to run the Query Builder.

This will produce the updated Master Table,

the report, and the chart as desired.

Query Builder Summary.

Combine tables.

Select only desired columns from tables.

Create columns with formulas and create reports.

Copy and paste the scripts of columns and reports into Post Query Script.

Scripting experience is not needed to build a Query Builder.

Creating a Query Builder is as simple as " point, click, drag and drop".

With this summary, that concludes my presentation.

Thank you.