Smart columns handling

Question

Folks,

How do you handle columns in a smart way?

Let's say I have a script and a table.

Script creates some columns in it.

While I work on script, I run it many times on a table. When script creates column, it ends up in creating many columns with the same name but different number. If I refer to created column by name later in the script, it would refer to the old columns (like "New Column", but not "New Column 23").

The way to deal with that is to delete a column with that name before actually creating. Problems starts when first using that script on fresh table - when there is no column to delete....

Anyways, how would you realize the smart column adding?

Some like:

I want to create a new column with this name. Is there any columns with this name or name that is this name and a number (dublicate columns)? If yes, then delete them and create my column. If there is no such columns, just create a new column with this name...

miguello · Answer

Oh, that was easy:

dt = Open( "$sample_data\Big Class.jmp" );

new_column = "BMI";

new_formula = "7 * :weight / :height / :height";

SmartColumn = function({new_column, new_formula},

//-----Start SmartColumn

expr_missing = Eval Insert ("col = dt << New Column( new_column, Formula( ^new_formula^ ));");

expr_exists= Eval Insert ("col = Column( new_column ) << Set Formula(^new_formula^) << EvalFormula;");

If( Is Missing( Is Scriptable( Try( Column( new_column ) ) ) ),

eval(parse(expr_missing));

, eval(parse(expr_exists));

);

//--End SmartColumn

);

SmartColumn(new_column, new_formula);

miguello · Answer

Ok, I think I nailed it.

If written as a function, it would be just a smart replacement of add column.

It would take column name, desired formula, and, if column with that name exists, update the formula a nd evaluate it, if it does not exist - create and set formula. No problems if that column is refered somewhere else in other formulas.

Can somebody help me with syntaxis of writing it as a ready to use formula?

Later I would add other column properties as arguments of function.

dt = Open( "$sample_data\Big Class.jmp" );

new_column = "BMI";

new_formula = "700 * :weight / :height / :height";

//-----Start SmartColumn

expr_missing = Eval Insert ("col = dt << New Column( new_column, Formula( ^new_formula^ ));");

expr_exists= Eval Insert ("col = Column( new_column ) << Set Formula(^new_formula^) << EvalFormula;");

If( Is Missing( Is Scriptable( Try( Column( new_column ) ) ) ),

eval(parse(expr_missing));,

eval(parse(expr_exists));

);

//--End SmartColumn

txnelson · Answer

My mistake on the missing "," in the code I sent you.  I apologize for that.

My intention, with the code, is to give you a way to detect if a given column is present.

If you have created a column that has a formula that uses the column you are wanting to delete, the simplest way to break that dependency is to turn the values in the column that has the formula pointing to the column you want to delete, into actual values, rather than formula based values.  All you have to do to do this is to remove the formula from the column, and JMP will convert the values to real data.  Once this is done, the original column can be deleted.

Names Default to Here(1);

dt = open("$SAMPLE_DATA\big class.jmp");

// Create a new column with a formula

dt << New Column("Ratio",formula(:Height/Weight));

// Remove the formula property and the data in the column

// is converted to real values, not virtural values

dt:Ratio << delete property("Formula");