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

- JMP User Community
- :
- Discussions
- :
- How do I compute the interval of the last row above that is not null?

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 30, 2020 7:47 AM
(318 views)

Use an example of this in the figure:

Thanks!

```
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt[2::8,"height"]="";
dt[10::17,"height"]="";
dt[19::28,"height"]="";
dt[30::39,"height"]="";
New Column("new",Character,"Nominal",Width(5));
```

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Aug 30, 2020 11:26 AM
| Last Modified: Aug 30, 2020 11:31 AM
(303 views)
| Posted in reply to message from lwx228 08-30-2020

- I don't think you want the new column called "New" to be a character column, nor do you want it to be "Nominal", thus I wrote my formula based upon those assumptions.
- You seem to be struggling with the concept of a JMP formula. Several of your Discussion entries have asked for help with solving some fairly simple issues. What can the Community do to help you for you to be able to create these formulas?
- Here is my solution......others may have different or better methods
`names default to here(1); dt = Open( "$SAMPLE_DATA/Big Class.jmp" ); dt[2 :: 8, "height"] = ""; dt[10 :: 17, "height"] = ""; dt[19 :: 28, "height"] = ""; dt[30 :: 39, "height"] = ""; New Column( "new", formula( If( Row() == 1, last = 0 ); If( Is Missing( :height ) == 0, If( last == 0, x = .; last = Row(); , x = Row() - last ); last = Row(); , x = . ); x; ) );`

Jim

11 REPLIES 11

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Aug 30, 2020 11:26 AM
| Last Modified: Aug 30, 2020 11:31 AM
(304 views)
| Posted in reply to message from lwx228 08-30-2020

- I don't think you want the new column called "New" to be a character column, nor do you want it to be "Nominal", thus I wrote my formula based upon those assumptions.
- You seem to be struggling with the concept of a JMP formula. Several of your Discussion entries have asked for help with solving some fairly simple issues. What can the Community do to help you for you to be able to create these formulas?
- Here is my solution......others may have different or better methods
`names default to here(1); dt = Open( "$SAMPLE_DATA/Big Class.jmp" ); dt[2 :: 8, "height"] = ""; dt[10 :: 17, "height"] = ""; dt[19 :: 28, "height"] = ""; dt[30 :: 39, "height"] = ""; New Column( "new", formula( If( Row() == 1, last = 0 ); If( Is Missing( :height ) == 0, If( last == 0, x = .; last = Row(); , x = Row() - last ); last = Row(); , x = . ); x; ) );`

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How do I compute the interval of the last row above that is not null?

Thank Jim!

I've always used Excel, and these simple data processes are easy.

I don't want to process data through Excel and then use a professional module that USES JMP.Instead, use JMP to handle all of them.

I think JMP can be done with this formula.But know how to write it

`Contains(:[Index(1,Row())],,-1)`

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How do I compute the interval of the last row above that is not null?

what are you expecting the results to be with this formula?

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How do I compute the interval of the last row above that is not null?

I'm just using this processed "Big Class. JMP "data to illustrate:

If the height column of a row is not empty, find the interval of the number of non-empty rows in the last row of the height column data above the row.

For example, dt[18," height "] is not empty, the last non-empty line at the top of line 18 is DT [9," height "].So dt[18,"new"] returns: 18 - 9 = 9

Thanks!

If the height column of a row is not empty, find the interval of the number of non-empty rows in the last row of the height column data above the row.

For example, dt[18," height "] is not empty, the last non-empty line at the top of line 18 is DT [9," height "].So dt[18,"new"] returns: 18 - 9 = 9

Thanks!

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How do I compute the interval of the last row above that is not null?

Is the formula I provided, not sufficient?

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How do I compute the interval of the last row above that is not null?

Parameters such as ">0" do not work in JMP, but excel does.

`Contains(:[Index(1,Row())],">0",-1)`

`Contains(:[Index(1,Row())],!is Missing(:),-1)`

I've tried all of them and they don't work.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How do I compute the interval of the last row above that is not null?

Created:
Aug 30, 2020 5:52 PM
| Last Modified: Aug 30, 2020 6:02 PM
(265 views)
| Posted in reply to message from lwx228 08-30-2020

Excel is like many languages, including JMP, that have their own unique coding shortcuts. Some of them become more universally used across many languages, but most just stay as unique syntax within their own language. Such syntactical items in JMP are all covered in the Scripting Guide.

Here is another way to do the calculation. It works fine on a small data table, but becomes costly with a lot of rows.

```
names default to here(1);
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt[2 :: 8, "height"] = "";
dt[10 :: 17, "height"] = "";
dt[19 :: 28, "height"] = "";
dt[30 :: 39, "height"] = "";
New Column( "new",
formula(
If( Is Missing( :height ) == 0,
curRow = Row();
x = curRow - Max( Current Data Table() << get rows where( Row() < curRow & Is Missing( :height ) == 0 ) );
,
x = .
);
x;
)
)
```

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How do I compute the interval of the last row above that is not null?

Thank Jim!

I use VBA and often write loops to complete my own projects.

I often use more than a few million lines of data.Speed is a key issue.

I feel like JMP is faster.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How do I compute the interval of the last row above that is not null?

Created:
Sep 7, 2020 7:08 AM
| Last Modified: Sep 7, 2020 7:13 AM
(134 views)
| Posted in reply to message from txnelson 08-30-2020

I want to add another column of "new2" to get it to the next qualifying interval.

How can calculate and print both columns ("new", "new2") simultaneously?Like VBA array, in memory um operation, once output to the table.

Thanks!