Creating a column in a loop with a variable in the formula.

[#1]

Hello,

I have been trying to create new columns inside a loop with a couple of variables in the formula.  The variables are column names that I am trying to subtract.  I have read similar threads with people having a similar issue but none of those solutions have worked in my case.  A snippet of my loop:

for (i=1, i<=n, i++,
S="Mean("||vars[i]||")";
S2="Wafer Mean("||vars[i]||")";
 ddt << Update(
 With(wt),
Match Columns(
:Lot = :Lot,
:Wafer = :Wafer,
:Recipe = :Recipe,
:LayoutSizeY = :LayoutSizeY,
Eval(cats) = Eval(cats)
),
Add Columns from Update table(
 wt:S2;
)
);
 ddt << New Column("Delta from wafer mean("||vars[i]||")", numeric, continuous, formula(ddt:S-ddt:S2));
 ddt << Run formulas;
);

The problem is that JMP is reading it literally and putting S in the formula rather than the string.  I tried various eval, evalexpr, expr, substitute commands but none have produced the required results.

Any other suggestions?

Thanks

[#2 · Accepted solution]

Hi @jjswan33,

Welcome to the community!

The solutions you looked into are all proper solutions for what you want to do, but you've got a couple problems in your script:

1. You don't need to scope the column reference to the table "ddt" in the formula because the scope is implied that the columns referenced in the formula are in the same table as the column  itself.  Putting "ddt" in there is going to hardcode that into the column formula, which means that formula won't work once ddt is no longer defined.

2. You need to wrap the column names in ":Name()" because you have special characters in the column name, specifically the parentheses.  For example, you can refer to a column "Var1" as :Var1, but you cannot refer to a column "Mean(Var1)" as :Mean(Var1); you would need to use :Name("Mean(Var1)").

Now, I am partial to Eval(Parse(Eval Insert())) because I think it's pretty easy to understand.  Let's start with the innermost part, Eval Insert, and then work our way outwards.

• Eval Insert() - use this to evaluate variables within a string.  Inside the string, put "^" characters around any variables you want to evaluate within the string.  For example:

  age = 30;
  Eval Insert("She is ^age^ years old");
  //Returns "She is 30 years old"

  If you need to use double quotes inside the string (you actually do in your case), then it can be helpful to start the string with "\[" and end with "]\".  This allows you to use double quotes freely inside the string.  Alternatively, you can use escape syntax to add double quotes (i.e. \!") inside the string.  Here's a string that should work for you:

  str = "\[ddt << New Column( "Delta from wafer mean(^vars[i]^)", numeric, continuous, formula( :Name("^S^") - :Name("^S2^") ) )]\";

• Parse() - converts the string into an expression.
• Eval() - evaluates the expression you parsed from the string.

To put it all together, do something like this:

str = "\[ddt << New Column( "Delta from wafer mean(^vars[i]^)", numeric, continuous, formula( :Name("^S^") - :Name("^S2^") ) )]\";

For( i = 1, i <= n, i++,
	S = "Mean(" || vars[i] || ")";
	S2 = "Wafer Mean(" || vars[i] || ")";
	ddt << Update(
		With( wt ),
		Match Columns(
			:Lot = :Lot,
			:Wafer = :Wafer,
			:Recipe = :Recipe,
			:LayoutSizeY = :LayoutSizeY,
			Eval( cats ) = Eval( cats )
		),
		Add Columns from Update table( wt:S2 )
	);
	Eval(Parse(Eval Insert( str )));
	ddt << Run formulas;
);

Note, I could have skipped defining string outside the loop and could have just stuck the string directly into Eval Insert within the loop.  However, I think this makes the your code a bit easier to debug.  If you're getting errors, with that, look at Parse(Eval Insert(str)) and see if the expression looks right.

Hopefully that helps!

[#3]

Thanks Cameron. I knew about the :Name issue but I was having trouble figuring out how to make it a quoted string, seems like the Eval Insert was what I was missing. I had tried parsing it as well but thought it was a bit dirty but I hadn't thought of defining the string outside the loop which makes it a pretty clean solution.