cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Using Lookup in JMP Example - Formula in Fat Type - Modified from Candy Bars
ryandewitt
Community Manager Community Manager

A quick way to lookup a value in a JMP data table is by using a formula applied to a column with an IF statement, to Lookup a specific row, or a specific  cell of data, in a column you would use a subscript after the Column name.

However this approach can tedious when the If clause is long or complex.

Similar to the VLookup function in Excel we can apply the same technique using JMP.

We do this by  creating a formula or by pasting a JSL script in the formula editor.

In my example with the Candy Bars sample data available from the Help menu in JMP,  I have a list of fat measurements in grams, and want to lookup then calculate, then assign them ranges from "No Fat" to "High Fat" .

Feel free to open this sample data and work along with me by pausing the video. There’s also a supporting and related blog entry linked below this video.

11842_Lookup in JMP.jpeg

So with this data table opened  I’m going to create a New Column by double clicking a blank header.

I’ll rename it to “Fat Type” by double clicking the Column header again.

From Column Info I’ll select “Character” because I’ll use a text string.

Then I’ll add a Formula to Lookup a range of values and assign a category based on the value.


Open a new script window from the File menu.

In JMP a list is designated as open and closed curly braces {} and the items in the list are separated by commas.

Enter the list of Fat Types as shown here:

{"No Fat", "Some Fat", "Low Fat", "Normal Fat", "Medium Fat", "High Fat"}

JMP has a handy function to find value within a matrix called Loc.  It has two arguments x and y.

In our example, x will be our matrix of values to look up and y will be the column we want to use to look up the values coming from the matrix.

In JMP a matrix is designated as open and closed square brackets [ ] and the elements in the matrix are separated by commas.

Next I’ll add the matrix of Total Fat in grams that we want to use to look up the values.

I’ll enter [0,1,5,10,20,25]

Not Narrated

See page 187 of the JMP Scripting Guide for more information on the loc functions and page 188 for the loc sorted function.)


Now we add this to the Loc sorted function as shown here

Not Narrated

loc sorted([0,1,5,10,20,25],:Total fat g)

To return the value found for each row in the lookup column and write it to the "Fat Type" column we use the row subscript function.

Simply enclose the loc sorted function within a square bracket ”[“ and an end with right bracket "]" as shown here.

[Loc Sorted([0, 1, 5, 10, 20, 25], :Total fat g)]

And here is the full script you can copy.

Not Narrated

{"No Fat", "Some Fat", "Low Fat", "Normal Fat", "Medium Fat", "High Fat"}[Loc Sorted([0, 1, 5, 10, 20, 25], :Total fat g)]

In the Formula Editor, paste that full script   and click apply.

Here is our new Column that lookups and organizes the Total Fat measurements column into six ranges.

You can now it for yourself by changing the matrix values with the linked data table below or your own data and enjoy using JMP.

Links

Lookup Tables in JMP- http://blogs.sas.com/content/jmp/2014/04/02/lookup-tables-in-jmp/