nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

JMP Scripting- How to change the value of a "cell"

rebecca-maceach

Hi,

I am wondering how to script it to change the value of a "cell" that is a result of a calculation in the script.

Specifically, I want to be able to change a value of a cell to "0" if the calculated value is negative while leaving all other "cells" alone.

Even if it needs to be in a new column, that is fine. For example, If i have my column of calculated values and the one I care about is negative, create a new column that would just say "0" for that "cell."

Thanks in advance.

Accepted answers

Justin_Chilton

Here is an example of how to conditionally change a cell when copying to a new column:

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
col = dt << New Column( "New Column", Formula( If( :age < 14, 0, :age ) ) );

In your case, you would change the condition to be less than zero rather than 14.

txnelson

Rebecca,

I am wondering if what you are asking needs to be explained at a more basic level than how Justin showed. His example does give you an answer, however, the way you described your problem, I can speculate on scenarios where the components of your conditional statement may be coming from sources other than from the current data table.

One of the basics in a JMP data table, is that any cell is directly addressable. Using the data table from Justin's answer, any of the cells can be accessed by just specifying the column name and row number.

&nbsp; :Age[1]

points to the first rows value for the column Age. A more correct way to reference it, is to use the data table pointer (data table namespace). So if the pointer to the data table as specified in Justin's response is "dt" then

&nbsp;&nbsp;&nbsp; dt:Age[1]

is the more proper form.

Taking Justin's example, we could actually change the values in place and not have to create a new column. The following code will do that

Names Default to Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
For( I = 1, I <= N Rows( dt ), I++,
&nbsp;&nbsp;&nbsp;&nbsp; if(dt:Age < 14,
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dt:Age = 0
&nbsp;&nbsp;&nbsp; );
);

ian_jmp

And building on Jim's reply, it's generally considered good practice to avoid loops if possible:

Names Default to Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
// Get the values of age into a vector
ageVals = dt:Age << getValues;
// Find the location of age values less than 14
ageValsLessThan14 = Loc(ageVals < 14);
// Set these values to zero
ageVals[ageValsLessThan14] = 0;
// Put the new values back into the column
dt:Age << setValues(ageVals);

All comments

Justin_Chilton

Here is an example of how to conditionally change a cell when copying to a new column:

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
col = dt << New Column( "New Column", Formula( If( :age < 14, 0, :age ) ) );

In your case, you would change the condition to be less than zero rather than 14.

txnelson

Rebecca,

&nbsp; :Age[1]

&nbsp;&nbsp;&nbsp; dt:Age[1]

is the more proper form.

Taking Justin's example, we could actually change the values in place and not have to create a new column. The following code will do that

Names Default to Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
For( I = 1, I <= N Rows( dt ), I++,
&nbsp;&nbsp;&nbsp;&nbsp; if(dt:Age < 14,
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dt:Age = 0
&nbsp;&nbsp;&nbsp; );
);

ian_jmp

And building on Jim's reply, it's generally considered good practice to avoid loops if possible:

Names Default to Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
// Get the values of age into a vector
ageVals = dt:Age << getValues;
// Find the location of age values less than 14
ageValsLessThan14 = Loc(ageVals < 14);
// Set these values to zero
ageVals[ageValsLessThan14] = 0;
// Put the new values back into the column
dt:Age << setValues(ageVals);

txnelson

I will take this one more step. Many time I have done similar things that Ian showed. Taking the data out of the data table and using matrix operators. This has an advantage of performance on large amounts of data. However the following is a technique that I have used many, many times, because it is very fast, and it doesn't require extracting from the data table.

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

Try( dt:Age[dt << get rows where( :Age < 14 )] = 0 );

The "Try" function simply protects against the case where the Get Rows Where could return an empty matrix, and the assignment would throw an error.

rebecca-maceach

Thank you all.

The what I am trying to do is change the value of a cell of a column that contains a formula.

The Big Class column "Age" is not from a formula so all these suggestions work to change the value of a cell.

I have a column, call it "Test," that uses the match formula. Is there a way to script it to change the value of a cell in that column to zero if the match formula calculates it as negative? I want to be able to force the value to zero if it is negative.

Would it make sense to make the test part of the formula?

If(Match(...) < 0,

Match(...)

)

txnelson

you can certainly make the conditional checking part of the formula

pmroz

Can you post the formula? As txnelson said you can simply make the checking part of the formula.

To post the formula:

1. Open your dataset and click on the + sign next to the formula column.

2. Double-click inside the formula window. This will change the window to show the JSL code for the formula

3. Copy the JSL code and post it to this forum.

The new formula will be something like this:

tmp = put-original-formula-here;

if (tmp < 0, 0, tmp);

KinKame

Ian,

I like the matrix way but how to do if I want to clean out several column for which cleaning condition are difference.

for example in big class

age --> set up all cell with value < 13 as empty

height --> set up all cell with value > 100 as empty

supposing that I have some where a reference table for all column condition.

Kerouwhack

Hi Jim,

What would be the form of this script if you had the "else" and third alternative as defined in the IF command?

Best,

txnelson

You have to change the formula to check for the value that you want to change, and then use a conditional statement in the formula to change it to the value you want.

// Sample formula
:A + :B;
If( isMissing(:A+:B) == 1, 0 );

ian_jmp

For this scenario, you could use the 'Range Check' column property. That way the table carries the meta data, and you don't need another table:

NamesDefaultToHere(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");

Wait(2);
Column(dt, "age") << rangeCheck( !LT( 13 ) );
Column(dt, "weight") << rangeCheck( LT( 100 ) );

johnpjust

one liner....

dt[dt<< get rows where(:ageval < 14)] = 0;