https://www.youtube.com/watch?v=yzYkQmbCD4g
Thanks Brady Brady and Stan, this video was based off a method from a Blog that BradyBrady posted and script edited by stanley.koprowski1 and john.ponte for clarifying the term "curly braces" used for JSL lists.
Brady Brady's Blog
http://blogs.sas.com/content/jmp/2014/04/02/lookup-tables-in-jmp/
Download my modified Candy Bars sample data for this tutorial here in our File Exchange.
https://community.jmp.com/docs/DOC-9999
JSL Script to paste in Formula Editor
{"No Fat", "Some Fat", "Low Fat", "Normal Fat", "Medium Fat", "High Fat"}[Loc Sorted([0, 1, 5, 10, 20, 25], :Total fat g)]
Narration Script
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, 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 using a creating a formula in the formula editor or by pasting a JSL script into the formula editor.
From the Candy Bars sample data, in this example I have a list of fat measurements in grams and want to calculate whether or not the fat measurements were rated using a scale from "No Fat" to "High Fat" entry.
You can find this sample data under JMP’s help menu and work along with me by pausing the video.
So with this data table opened I’m going to create a New Column by double clicking an empty Column header.
I’ll rename it to “Fat Type” by double clicking the Column header again.
From this same Column Info window I’ll select this “Character” type 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 and type the list of Fat Types:
File New Script.
Enter as I have it here and available below video.
{“No Fat", "Some Fat", "Low Fat", "Normal Fat", "Medium Fat", "High Fat"}
Next we’ll add the matrix values of Total Fat in grams that we want to look up the values:
[0,1,5,10,20,25]
JMP has a handy function to find value within a list or matrix called Loc. It has two arguments x and y.
In our example, x will be our list of values to look up and y will be the column we want to look for the values
(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 like I have it here, and you can copy and paste from text below the video:
loc sorted([0,1,5,10,20,25],:Total fat g)
The full script should look like this and be pasted in the Formula Editor, yes you can paste JSL script into a a Formula.
{"No Fat", "Some Fat", "Low Fat", "Normal Fat", "Medium Fat", "High Fat"}[Loc Sorted([0, 1, 5, 10, 20, 25], :Total fat g)]
Select apply.
And here’s our Column result for Fat Types.
Try it for yourself with the sample data and enjoy using JMP.
