Subscribe Bookmark RSS Feed

Insert Column Variable into Formula

robot

Community Trekker

Joined:

Feb 27, 2012

Hi,

I am trying to add a script to a data table that will allow the user to parse a given given column (:File Name) by delimiters which the user will select by a display box (delim).  Ideally, the formula will continue to parse the :File Name column until there is nothing left to parse.  A block of my script is below.  In it, I create a new column (col) for each parsed segment of :File Name.  I then check col for missing values and then stop if the entire column is empty.

My problem is that I can not get col to evaluate in the formula.  I have even tried using MS 's Eval(Parse(Eval Insert())) trick, but I can not get it to work.  Are there any suggestions?  I am using JMP11.

Thanks!

// Example script.

// Note: col = Column( "Column 442" );

delim = "-";

i = 1;

j = 1;

While( j > 0,

  j = 0;

  col = dt << New Column();

  col << Data Type(Character, Nominal);

  col << Set Formula(Word( i, :File Name, delim ));

  For Each Row( If( Is Missing(:Column 442) == 0, j = j + 1 ) ); // This works.

  Eval(Parse(Eval Insert("For Each Row( If( Is Missing(:Column 442) == 0, j = j + 1 ) )")));  // This works.

  Eval(Parse(Eval Insert("For Each Row( If( Is Missing(^col^) == 0, j = j + 1 ) )"))); // This does not work.

  Eval(Parse(Eval Insert("For Each Row( If( Length(^col^) != 0, j = j + 1 ) )"))); // This does not work.

  i = i + 1;

);

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

The variable col is a reference to a column. But in a ForEachRow() loop we need to access not the column but its values.  Both alternatives below should work in your code. Note the empty index [ ] in the second example.

Eval(Parse(Eval Insert("For Each Row( If( Is Missing(as column(^col^)) == 0, j = j + 1 ) )")));

Eval(Parse(Eval Insert("For Each Row( If( Is Missing(^col^[]) == 0, j = j + 1 ) )")));

4 REPLIES
David_Burnham

Super User

Joined:

Jul 13, 2011

ok I'm not gonna try and understand your code, but regarding syntax, you say that the following works:

Eval(Parse(Eval Insert("     

      For Each Row( If( Is Missing(:Column 442) == 0, j = j + 1 ) );

              

")));


The content inside the Eval pattern is a string, so the variable you put inside the ^...^ will act like a string so to generalise it you want to so something like the following

colName = col << Get Name;   // e.g. colName = "Column 442"

Eval(Parse(Eval Insert("    

      For Each Row( If( Is Missing(:^cName^) == 0, j = j + 1 ) );

             

")));



-Dave
Solution

The variable col is a reference to a column. But in a ForEachRow() loop we need to access not the column but its values.  Both alternatives below should work in your code. Note the empty index [ ] in the second example.

Eval(Parse(Eval Insert("For Each Row( If( Is Missing(as column(^col^)) == 0, j = j + 1 ) )")));

Eval(Parse(Eval Insert("For Each Row( If( Is Missing(^col^[]) == 0, j = j + 1 ) )")));

robot

Community Trekker

Joined:

Feb 27, 2012

Thanks MS!

David_Burnham

Super User

Joined:

Jul 13, 2011

Also remember that :col refers to a column as a whole.  Inside a For Each Row loop you need to use the [] notation to reference the current cell (one of many reasons not to use For Each Loops!).

-Dave