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.
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/