294737_en-US

liliconcom

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

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

ScriptingRe: How do I compute the interval of the last row above that is not null?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.Re: How do I compute the interval of the last row above that is not null?

Thanks!

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.

 

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

 

Thanks!

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.

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

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

Is the formula I provided, not sufficient?

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!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?

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.

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)

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