nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

lwx228

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

Accepted answers

txnelson

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

All comments

txnelson

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

lwx228

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)

lwx228

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.

txnelson

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

lwx228

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!

txnelson

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

lwx228

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.

lwx228

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!

lwx228

Thanks!

txnelson

It is a simple matter to move a column data into a matrix, and then generate new matrices in the format that you want the new columns to be, and then once complete, move the matrices to the data table. All of the matrix manipulations will be done in memory.