topic Using specific row values for use in a formula with JSL in Discussions

Using specific row values for use in a formula with JSL

scottahindle — Fri, 09 Jun 2017 09:22:44 GMT

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.

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

txnelson — Fri, 09 Jun 2017 10:29:46 GMT

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>

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

guy_yosef — Fri, 09 Jun 2017 15:09:34 GMT

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

)

);

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

txnelson — Fri, 09 Jun 2017 15:14:38 GMT

Is there a question?

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

scottahindle — Wed, 14 Jun 2017 07:27:06 GMT

Thank you, Jim!

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

scottahindle — Wed, 14 Jun 2017 07:30:28 GMT

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.