Subscribe Bookmark RSS Feed

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

rebecca-maceach

Community Trekker

Joined:

Mar 5, 2015

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.

3 ACCEPTED SOLUTIONS

Accepted Solutions
Justin_Chilton

Joined:

Aug 27, 2015

Solution

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.

Justin
txnelson

Super User

Joined:

Jun 22, 2012

Solution

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.

  :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

    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++,
     if(dt:Age < 14,
        dt:Age = 0
    );
);

 

Jim
ian_jmp

Staff

Joined:

Jun 23, 2011

Solution

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);
12 REPLIES
Justin_Chilton

Joined:

Aug 27, 2015

Solution

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.

Justin
txnelson

Super User

Joined:

Jun 22, 2012

Solution

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.

  :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

    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++,
     if(dt:Age < 14,
        dt:Age = 0
    );
);

 

Jim
ian_jmp

Staff

Joined:

Jun 23, 2011

Solution

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

Super User

Joined:

Jun 22, 2012

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.

Jim
KinKame

Community Trekker

Joined:

Nov 30, 2015

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.

ian_jmp

Staff

Joined:

Jun 23, 2011

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 ) );
Kerouwhack

Occasional Contributor

Joined:

Jul 20, 2017

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,

K

rebecca-maceach

Community Trekker

Joined:

Mar 5, 2015

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.

ms

Super User

Joined:

Jun 23, 2011

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

If(Match(...) < 0,

    0,

    Match(...)

)