<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />I frequently get the question of how to script a formula (apply the same formula to several columns, using something about the reference column in the formula). This is usually an entry place for people to start scripting too, so they are novices.
I recently posted a question in the regular discussion section, trying to both get my customer to participate (they did not) and some expert advice (anyone reading this). I'd really like to generate a solid answer I could point folks to when this question comes up. For example, is the suggested answer really the best way to do it if you have 1000+ columns, etc.?
Any help on this would be appreciated. Or a blog entry. Or pointing out this has been covered somewhere else?
The regular discussion is here:
Best way to script a column formula into many columns?
Thank you!
As a follow-up to the previous: you'll notice I use the :Name() function for column names. This helps avoid any issues arising from having interesting characters within column names. Not always needed, but again, bulletproof.
Following up what Don said, I often find myself taking it a step further, using Eval(Parse(Substitute())).
This is, in effect, actually writing the code dynamically. While somewhat inelegant, it is nearly bulletproof. This construct isn't always the most efficient (due to the character substitutions), but it is the one that succeeds when all of the other tricks fail, so it is good to know about. The most significant drawback is that you cannot see the color coding due to the quoting:
Names Default To Here(1);
dt = open("$Sample_Data/football.jmp");
codeFrag = "\[
New Column( "_colName_ Rank from mean",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( Col Rank( Abs( Col Mean( :Name( "_colName_" )) - :Name( "_colName_" ) ) ) )
)
]\";
colList = dt<<getcolumnnames(string);
for(i=1, i<=nitems(collist), i++,
if(column(dt ,colList[i])<<get modeling type == "Continuous",
eval(parse(substitute(codeFrag, "_colName_", colList[i])))
)
);
If the lack of color coding bothers you, you can maintain it by wrapping the string in a char(nameexpr()) construct, as below. This adds complexity but it may be worth it while writing the code for debugging purposes... once it works you could always strip out the char(nameexpr()) piece and place in to quotes using "\[ ]\".
Bottom line: this will not win a beauty contest, but when you write the string yourself, parse it and evaluate it, you WILL get what you're after. Keep this in your back pocket for when Eval(EvalExpr( ...... Expr() .... )) fails and you're not able to figure out (or don't have the time to figure out, or don't care) why.
Names Default To Here(1);
dt = open("$Sample_Data/football.jmp");
codeFrag = char(nameexpr(
New Column( "_colName_ Rank from mean",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( Col Rank( Abs( Col Mean( :Name( "_colName_" )) - :Name( "_colName_" ) ) ) )
)
));
colList = dt<<getcolumnnames(string);
for(i=1, i<=nitems(collist), i++,
if(column(dt ,colList[i])<<get modeling type == "Continuous",
eval(parse(substitute(codeFrag, "_colName_", colList[i])))
)
);
Cheers,
Brady
Laura,
I think the answer given in the Forum is a solid approach that doesn't complicate the code any more than it has to. I think the use of Expr and Name Expr are unavoidable in this situation. As a next step, you might consider removing the formula from the loop to make the code more generic. This way, you can create a function that takes formulas as an argument (see attached JSL). There's probably a simpler way to do this, but I'm so accustom to using Substitute (and it's extremely robust). Not sure why the [Row()] part is needed, but it is for anytime you're referencing a column except inside a statistical column function. Hope this provides some insight.
Don
