topic Re: JMP Scripting- How to change the value of a "cell" in Discussions

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

rebecca-maceach — Fri, 04 Dec 2015 19:36:26 GMT

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.

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

Justin_Chilton — Fri, 21 Jul 2017 20:36:38 GMT

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.

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

txnelson — Fri, 21 Jul 2017 20:39:53 GMT

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

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

ian_jmp — Fri, 21 Jul 2017 20:40:44 GMT

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

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

txnelson — Sat, 05 Dec 2015 19:39:56 GMT

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.

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

rebecca-maceach — Mon, 07 Dec 2015 21:12:34 GMT

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.

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

ms — Mon, 07 Dec 2015 21:27:33 GMT

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

If(Match(...) < 0,

Match(...)

)

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

txnelson — Tue, 08 Dec 2015 01:52:31 GMT

you can certainly make the conditional checking part of the formula

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

pmroz — Tue, 08 Dec 2015 13:51:17 GMT

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

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

KinKame — Fri, 02 Sep 2016 03:24:39 GMT

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.

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

Kerouwhack — Thu, 20 Jul 2017 21:52:58 GMT

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,

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

txnelson — Thu, 20 Jul 2017 23:19:37 GMT

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

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

ian_jmp — Fri, 21 Jul 2017 09:40:16 GMT

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

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

johnpjust — Sat, 28 Jul 2018 06:06:21 GMT

one liner....

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