turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Using specific row values for use in a formula wit...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 9, 2017 2:22 AM
(3410 views)

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.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 9, 2017 2:54 AM
(6753 views)

Solution

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

5 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 9, 2017 2:54 AM
(6754 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 9, 2017 8:09 AM
(3393 views)

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

**)**

**)**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 9, 2017 8:14 AM
(3389 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 14, 2017 12:27 AM
(3350 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 14, 2017 12:30 AM
(3349 views)

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.