Creating a JMP formula to pull values from a look up table with four categorical variables

[#1]

Hi all,

I am developing habitat suitability indices for 75 different bird species. I have tables with the same structure as the one described here (and attached as a JMP data table) for each bird species. I will stack them by species so that all species are included in the same table. The attached table is hierarchical. The first level of the hierarchy is species (currently only one is there, but eventually data for all 75 species will be in the same table). The next level of the hierarchy is landform (e.g., valley, terrace, or ridge). Once landform has been determined, the next level of the hierarchy is land cover type (e.g., deciduous, coniferous, mixed forest). Once the landform / landcover combination has been determined, the next level is timber class (e.g., shrub-seedling, sapling, pole saw timber, large saw timber). Each 4-way combination of species, landform, landcover, and timber class has a habitat suitability value between 0 and 1. For example, Worm-eating Warblers, who like older deciduous forests have high habitat suitability value (0.8) for the valley / deciduous / large saw timber combination. This same species has a lower habitat suitability value (0.2) for the ridge / mixed forest / sapling combination. If you look at the attached table this will make sense to you.

I would like to be able to create a formula that I can use as a column property that references this look up table to find habitat suitability values based on the species / landform / landcover / timber class combination for any given site (of which I have thousands). My first thought was to do this with an elaborate if then statement, but there are 60 landform / landcover / forest age combinations for each species and 75 species. I don't know how to do this any way other than manually in the formula editor and this seems crazy. Is there a simple, more efficient way to create a column formula that can reference this look up table to pull suitability values based on unique combinations of the four hierarchical categorical data columns: species / landform / land cover / timber class? 

I'd be grateful for any help with this. I saw an older post from @brady_brady about how to create a formula for a much more simple lookup table, but I couldn't figure out how it might work for a hierarchical data set like this. GO, JMP community, GO!!! 

[#2]

Here are two ways of handling your lookup.  The first is a formula that I created and applied to a sample table with just 3 entries.  It looks up in the WEWA suitability matrix data table and pulls in the suitability value.  It is a fine formula, but not very efficient.

Here is the formula used for the column "Formula durived Suitability"

If( Row() == 1,
dt = Data Table( "WEWA suitability matrix with data" );
dtLookup = Data Table( "WEWA suitability matrix" );
Show( dt, dtlookup );
);
curSpec = Uppercase( dt:Species );
curLandForm = Uppercase( dt:Landform );
curLandCover = Uppercase( dt:Landcover );
curTimber = Uppercase( dt:Timber Class );
x = .;
Try(
x = dtLookup:Suitability[dtLookup << get rows where(
Uppercase( dtLookup:Species ) == curSpec & Uppercase( dtLookup:Landform ) == curLandForm & Uppercase( dtLookup:Landcover ) ==
curLandCover & Uppercase( dtLookup:Timber Class ) == curTimber
)][1],
.
);
x;

The second method, that creates the column called "suitability" is created by using the Update platform in JMP..  Here is the script for it's creation

names default to here(1);
Data Table( "Observed Species" ) << Update(
	With( Data Table( "WEWA suitability matrix" ) ),
	Match Columns(
		:Species = :Species, :Landform = :Landform, :Landcover = :Landcover,
		:Timber Class = :Timber Class
	)
);

P.S.   The data table called Observed Species starts out with only Sepcies, Landform, Landcover and Timber Class as the columns.  

[#3]

Hey @txnelson 

Both of your solutions make sense to me. I'd prefer the formula driven solution. I went ahead and copied the formula from your "formula derived.." column in the Observed Species table you created into a second JMP table where I am actually calculating the habitat suitability indices (attached). When I try to apply the formula I get the following error: 

I'm not sure how I would fix this. Would you mind taking a look at the "formula derived suitability column) in the attached JMP data table and letting me know where I might have made an error? Thanks again for your help. Casey

[#4 · Accepted solution]

There are a couple of issues.  I do not know what version of JMP you are running with.  I developed the formula in JMP 16, which apparently has a bug.  Therefore the formula was changed when I saved it in JMP 16, from what I had tested it with.  I will be reporting the bug after I finish responding to you.  The correct formula to use is:

If( Row() == 1,
	dt = Data Table( "WEWA suitability matrix with data" );
	dtLookup = Data Table( "WEWA suitability matrix" );
	Show( dt, dtlookup );
);
curSpec = Uppercase( dt:Species );
curLandForm = Uppercase( dt:Landform );
curLandCover = Uppercase( dt:Landcover );
curTimber = Uppercase( dt:Timber Class );
x = .;
Try(
	x = dtLookup:Suitability[dtLookup << get rows where(
		Uppercase( dtLookup:Species ) == curSpec & Uppercase( dtLookup:Landform ) == curLandForm & Uppercase( dtLookup:Landcover ) ==
		curLandCover & Uppercase( dtLookup:Timber Class ) == curTimber
	)][1],
	.
);
x;

I also edited this formula in my last response, so no one should have an issue with it.

In the latest data you supplied, there is a mismatch with the lookup data table.  You have changed some of the lowercase letters to uppercase.  The original formula I wrote does not compensate for this.  In the current version, I forced all of the lookups to be done with uppercase so that should no longer be an issue.

[#5]

EDIT:  OOPS, I missed the part about your wanting this in a table as a column property--I thought you just wanted to return a value based on someone selecting the 4 values in a dialog or something. Hence, you should go with something like Jim proposes.

Cheers,

Brady

//////////////////

Hi,

There are many ways to do this... probably the most "textbook" way involves an associative array with multidimensional keys, but for your use case it is much more straightforward to do something like the following:

Names Default To Here( 1 );

//suppose these are the values you want to look up, and you've gotten them somehow
spec = "Worm-eating warbler";
form = "Terrace-mesic";
cover = "Mixed";
class = "sapling";

//set a variable reference to the lookup table
dt1 = Data Table( "WEWA suitability matrix.jmp" );

//add a formula column to the lookup table, concatenating these 4 values, separated by a comma for clarity
dt1 << New Column( "Concat", character, formula( Concat Items( {:species, :landform, :landcover, :timber class}, "," ) ) );

//create a matrix of the suitabilities and a list of the combo values
suits =  dt1:suitability << get values;
combos = dt1:concat << get values;

//create a lookup value from your 4 values by concatenating them in the same way you did in the formula
myLookup = concatitems({spec, form, cover, class}, ",");

//grab the suitability value you want; it will have the same position in its matrix as the lookup value has in its list
s = suits[contains(combos, myLookup)];

show (s);

Cheers,

Brady

[#6]

You could maybe also try using Virtual Join.

Create linking column (for example concatenate all four grouping values) to both of tables you have:

And then perform virtual join:

Table with suitability as Link ID:

Table which you want to pull stuff to:

If I remember correctly virtual joins did require you to have at least the datatable which will be Link ID saved.

Essentials to understanding Virtual Join in JMP 

JMP On Air Virtual Joins