10284_en-US

liliconcom

Insert Column Variable into Formula

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.
&nbsp; Eval( Parse( Eval Insert( "For Each Row( If( Is Missing(:Column 442) == 0, j = j + 1 ) )" ) ) );&nbsp; // This works.
&nbsp; Eval( Parse( Eval Insert( "For Each Row( If( Is Missing(^col^) == 0, j = j + 1 ) )" ) ) ); // This does not work.
&nbsp; Eval( Parse( Eval Insert( "For Each Row( If( Length(^col^) != 0, j = j + 1 ) )" ) ) ); // This does not work.
&nbsp; i = i + 1;
);

 

Re: Insert Column Variable into Formula<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

Thanks MS!

Re: Insert Column Variable into Formula

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!).

Re: Insert Column Variable into Formula

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

Re: Insert Column Variable into Formula

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

             

")));