Choose Language Hide Translation Bar
Highlighted
lwx228
Level VII

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

Use an example of this in the figure:

Thanks!

2020-08-30_22-32-48.png

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
txnelson
Super User

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

  1. 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.
  2. 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?
  3. 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

View solution in original post

11 REPLIES 11
Highlighted
txnelson
Super User

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

  1. 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.
  2. 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?
  3. 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

View solution in original post

Highlighted
lwx228
Level VII

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
txnelson
Super User

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
lwx228
Level VII

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!
Highlighted
txnelson
Super User

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
lwx228
Level VII

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
txnelson
Super User

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

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
lwx228
Level VII

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
lwx228
Level VII

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

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

2020-09-07_21-44-21.png

 

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

 

Thanks!

Article Labels