cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
scottahindle
Level IV

Using specific row values for use in a formula with JSL

I would like to do something that I learnt a long time ago in VB, but am struggling with JMP scripting language. I am new to JMP.

I would like to run a For loop in a formula but use a cell array.

 

For example, I know I can reference a column as follows: j = Column ("a");

If I then say j[4] = 77 + 2;   I will get the value 79 in column “a”, row 4.

What, though, if I wanted to run a loop on Column "a", for example For(i=1,i<=20,i++, SOME FORMULA USING j[i]);

 

In the attachment I would like to use column-B Row [1] with column-C’s Rows 1 to 5. And then, column-B Row [6] with column-C’s Rows 6 to 10. My aim is to use the sequence in Column-A as an identifier, hence each time the sequence is of value 1 I hold this value for 5 subsequent uses of the formula where what goes into Column-D depends on the aritmetic performed on the contents of Columns B and C. (For example Column-D=Column-B-Value + Column-C-Value.) See the attachment to see in a picture what I mean.

 Any help would be great and much appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Using specific row values for use in a formula with JSL

The following formula works for your attached example

targetrow = (Row() - :Name( "Column-A" )) + 1;
:Name( "Column-B" )[targetrow] + :Name( "Column-C" );

Remember, the formula will always loop through all of the rows, and the function, Row(), will always give you the row that is currently being worked on.  When using a formula in JMP, one needs to look at what results are needed when looking at a given row.  It is a different paradigm than VB

Here is another version, doing the For looping you are looking for

If( :Name( "Column-A" ) == 5,
	For( i = Row() - 4, i <= Row(), i++,
		:Name( "Column-D" )[i] = :Name( "Column-B" )[Row() - 4] + :Name( "Column-C" )[i]
	)
);
// The last value calculated will be the value for the current row, 
// so it needs to be handled as a special case
:Name( "Column-B" )[Row() - 4] + :Name( "Column-C" )[Row()];

I guess that technically, you could approximate a VB world, if one bypassed all processing for all of the rows, until the last row was detected, and then you could go back and loop through all of the rows under program control.....that seems like a lot of work

dt=current data table();
If(Row()==N Rows(dt),
<Do whatever processing you want>
);
<Process for the current(last) row>
Jim

View solution in original post

5 REPLIES 5
txnelson
Super User

Re: Using specific row values for use in a formula with JSL

The following formula works for your attached example

targetrow = (Row() - :Name( "Column-A" )) + 1;
:Name( "Column-B" )[targetrow] + :Name( "Column-C" );

Remember, the formula will always loop through all of the rows, and the function, Row(), will always give you the row that is currently being worked on.  When using a formula in JMP, one needs to look at what results are needed when looking at a given row.  It is a different paradigm than VB

Here is another version, doing the For looping you are looking for

If( :Name( "Column-A" ) == 5,
	For( i = Row() - 4, i <= Row(), i++,
		:Name( "Column-D" )[i] = :Name( "Column-B" )[Row() - 4] + :Name( "Column-C" )[i]
	)
);
// The last value calculated will be the value for the current row, 
// so it needs to be handled as a special case
:Name( "Column-B" )[Row() - 4] + :Name( "Column-C" )[Row()];

I guess that technically, you could approximate a VB world, if one bypassed all processing for all of the rows, until the last row was detected, and then you could go back and loop through all of the rows under program control.....that seems like a lot of work

dt=current data table();
If(Row()==N Rows(dt),
<Do whatever processing you want>
);
<Process for the current(last) row>
Jim
guy_yosef
Level III

Re: Using specific row values for use in a formula with JSL

I am using this script to color in yellow a cell if last 30 days average is more then +1 sigma compare to target

 

 

For( i = 1, i <= N Row( dt ), i++,

If( Column( dt, "Mean 30 days" )[i] > ((Column( dt, "Target" )[i]) + Column( dt, "Std" )[i] ),

Column( dt, "Mean 30 days" ) << Color Cells( 9, {i} )

)

);

txnelson
Super User

Re: Using specific row values for use in a formula with JSL

Is there a question?

Jim
scottahindle
Level IV

Re: Using specific row values for use in a formula with JSL

Thank you, Jim!

scottahindle
Level IV

Re: Using specific row values for use in a formula with JSL

In the end it took me a while to work out the way JMP scripting works. The attachments show what I have done, which works for me. (The example deviates from the simple example I used to start this discussion.)

If anybody has any tips on how to approach this kind of problem differently please comment. Having used for loops and if conditions in basic VB programming for so many years, and being new to JMP, means I may need guidance on better understanding how to approach JMP scripting.