Showing results for 
Show  only  | Search instead for 
Did you mean: 
How to assign a category using a lookup table in JMP®


Using the JMP Scripting Language (JSL) you can write a script that assigns values based upon data stored in a different data table. The following example script demonstrates how you can use a lookup table to assign levels of schooling based upon the age of a student. The script does the following:

  1. Open the original data table containing a list of students and their ages.
  2. Add a new column to the original data table to store the school value.
  3. Create a lookup table containing the age ranges for the school levels.
  4. Loop through each row of the original data table:
    • Obtain the row number in the lookup table where the age falls between the age range.
    • Assign the school value from the lookup table row to the new column in the original data table.


In a JMP session, click the File menu and select New ► Script. Copy the below script and paste it into the script window. To run the sample script, click the Edit menu and select Run Script.


 /* Open a sample data table */ 
dt = Open( "$SAMPLE_DATA\Big" ); 

/* Add a column for the assignment */ 
dt << New Column( "School", Character ); 

/* Create a lookup table */ 
lt = New Table( "SchoolLookup",
	Add Rows( 3 ),
	New Column( "SchoolLevel", Character, Nominal, Set Values( {"Elementary", "Middle", "High"} ) ),
	New Column( "Low", Numeric, Continuous, Format( "Best", 12 ), Set Values( [5, 13, 15] ) ),
	New Column( "High", Numeric, Continuous, Format( "Best", 12 ), Set Values( [12, 14, 18] ) )

/* Loop through each row of the original data table */ 
For( i = 1, i <= N Rows( dt ), i++, 
	/* Obtain a matrix contining the row number of the lookup table where the age falls within the range */
	ageRange = lt << Get Rows Where(
		Column( lt, "Low" )[] <= Column( dt, "age" )[i] & Column( dt, "age" )[i] <= Column( lt, "High" )[]
	/* Extract the row number from the matrix */ 
	r = ageRange[1];
	/* Assign the school value to the original data table */
	Column( dt, "School" )[i] = Column( lt, "SchoolLevel" )[r];


Additional Documentation

More information about the JMP Scripting Language can be found in the JMP Scripting Guide. You can access the guide from within JMP by clicking on the Help menu and select JMP Help.  From the online Table of Contents, choose Scripting Guide.


[Previously JMP Note 46535]


alternative1: use Tables/Update
difference: uses  r= ageRange[Length(agerange)] instead of agerange[1]

alternative2: use Tables/Join

difference: generates a new table

Operating System
macOS Windows
Products JMP JMP Pro