topic Re: JSL: Creating formula using a variable column name in Discussions

JSL: Creating formula using a variable column name

Thu, 31 Aug 2017 08:24:18 GMT

Hi,

I want to do an actually simple thing (at least I thought so), namely to create a new column using JSL with a formula that contains a variable column name. I already tried several combinations of the commands "column, as column, name, as name, eval, parse .." and I searched the forum, but everything I tried results in an error that the formula could not be evaluated.

An example what I want to do:
"column 1" is a column in my data table.

a = "column 1";
new column("test", formula(a*2));

The column "test" should contain the formula that multiplies "column 1" with 2.

Thanks for any help,
Dahla

Re: JSL: Creating formula using a variable column name

Fri, 18 Aug 2017 21:27:09 GMT

Sometimes the simplest things can be mindboggling in JSL :)

Here are a couple of approaches:

new column("test",formula( as column("column 1")*2 ));

a = "column 1";
new column("test",formula( a[]*2 ));

The logic of the syntax is something like this. In the first example we want to treat "column 1" as a column of numbers. In the second example, "[]" indicates that a is an array of numbers.

Hope this helps

Re: JSL: Creating formula using a variable column name

Mon, 16 Nov 2009 08:10:00 GMT

Thanks for the answer, but unfortunately it doesn't help.

The first approach is not what I wanted because I need to take the column name out of a variable (it can change).

The second approach does not work, because JMP doesn't insert the real column name into the formula. If you go into the dialog for changing the formula, it reads "a[Empty()] * 2" in your suggestion, and I need something like ":Column1 * 2" there.

Any other suggestions?

Re: JSL: Creating formula using a variable column name

Fri, 18 Aug 2017 21:28:02 GMT

I think my 2nd example should have read like this:

a = Column("column 1");
new column("test",formula( a[]*2 ));

Re: JSL: Creating formula using a variable column name

Mon, 16 Nov 2009 14:53:06 GMT

Thank you, that works!

Re: JSL: Creating formula using a variable column name

Thu, 31 Aug 2017 08:26:00 GMT

I still have an additional question:

What I finally want to do is to create a new column for each item of a list. In Stig's solution the column formula still depends on the variable "a[]" and not (as I'd prefer) on the column name "Column 1", so I'm not allowed to change "a[]" in the following script without changing the calculated column values as well. That means my following approach does not work (the length of collist and the column names can change, so I need a loop):

collist = {"column 1", "column 2"};

for (k=1, k<=length(collist), k++,
a = Column(collist);
new column(concat("test",char(k)), formula( a[]*2 ));
);

Directly inserting a list element in the formula() statement does not work either. Does anyone have another suggestion?

Dahla

Re: JSL: Creating formula using a variable column name

Thu, 31 Aug 2017 08:26:42 GMT

If I use the following code:

dt = Current Data Table();
a = Column("height");
dt << New Column("test1",formula(a[]*2));
a = Column("weight");
dt << New Column("test2",formula(a[]*2));

both columns test1 and test2 are computed correctly even though the formula for both reference the same variable a. So in principle you shouldn't have a problem with the code within a loop. However, this approach doesn't work if you want the new columns to retain their behaviour in the table as computed columns - which leaves open the question still open of how to generate from code a column with the formula ":column 1 * 2" ...

Re: JSL: Creating formula using a variable column name

Wed, 18 Nov 2009 07:16:43 GMT

Oh, I see only now that my previous post was not fully displayed... Sorry for that.

Yes, I need the columns to retain their behaviour or at least the values. Thats why I had the idea to remove the formulas directly after calculating them. But I had the problem that I did not find a statement that does the same as "Formula... -> Clear", which means to remove the formula and retain the values as they are. Does someone know how this works?
That would not be the nicest solution, but enough for me at the moment.

Re: JSL: Creating formula using a variable column name

Wed, 18 Nov 2009 13:26:03 GMT

I now made the workaround to read the values out of the column into a vector and write the calculated values in a new column.

But I'm still interested in answers for the open questions.

Dahla

Re: JSL: Creating formula using a variable column name

mpb — Sun, 15 Oct 2017 13:40:38 GMT

After trying to come up with a working solution I finally gave up and asked JMP support how this might be done. Wendy kindly showed me the key lines and I embedded them in a for loop as a demo. The code I am including assumes that the current data table has 2 columns named abc and def which have a few rows of data of your choice. By the way there exists a new SAS Press book called Jump into JMP Scripting by Wendy Murphrey and Rosemary Lucas which is a very helpful book on scripting. It does cover Expr and Eval Expr used in the example.

dt = Current Data Table();
cols = {abc, def};
nlist = N Items(cols);
 
For( i = 1, i <= nlist, i++,
     newColExpr = Expr(
          dt << New Column( char(cols[i])||"*2", Numeric, Formula( Expr( cols[i] ) * 2 ) )
     );
     Eval( Eval Expr( newColExpr ) );
)

Re: JSL: Creating formula using a variable column name

Thu, 31 Aug 2017 08:28:04 GMT

I tried but cant get it work on a variant.

There are two column where the user chooses in a custom dialog box, and i assign them the names of lx,ly.
I need a new column with formula

Radius = SQRT((X^2)+(Y^2))

However, when i run it, althought the new column is created, JMP prompt me
"N Items() argument must be a list", and no formula created.

Appreciate if a kind soul can advice on which part is faulty. Thanks.

customDlg << CloseWindow;
lp = colListP << GetItems;
lx = colListX << GetItems;
ly = colListY << GetItems;
 
// Are the user selections viable?
if (NItems(lp) < 1,
     Dialog("ERROR: Select one or more Parameter Columns", Button("OK")); Throw());
if (NItems(lx) != 1,
     Dialog("ERROR: You need to select an X Column", Button("OK")); Throw(),
     xVar = lx(1)     
     );
if (NItems(ly) != 1,
     Dialog("ERROR: You need to select a Y Column", Button("OK")); Throw(),
     yVar = ly(1)
     );
 
 
// *********************************************************************************
// Build New Column with new formula
// *********************************************************************************
 
dt = Current Data Table();
a = Column(dt,xvar);
b = Column(dt,yvar);
colx = ;
coly = ;
nlist = N Items(a);
For( i = 1, i <= nlist, i++,     
newColExpr = Expr(          
               dt << New Column( "Radius", Numeric,
               Formula( Root((Power(Expr(a(i)),2)+Power(Expr(b(i)),2)),2 ) )     );     
               Eval( Eval Expr( newColExpr ) );
);
);
);

Re: JSL: Creating formula using a variable column name

mpb — Wed, 04 Jan 2017 14:52:38 GMT

Here's an example using the Column Dialog which gives the user the choice to select exactly 2 columns from which the Radius column will be created.

dt = Current Data Table();
cdlg = Column Dialog( clist = ColList( "Pick 2 Columns", Min Col( 2 ), Max Col( 2 ) ) );
cols = cdlg["clist"];
 
newColExpr = Expr( dt << New Column( "Radius", Numeric, Formula( Sqrt( Expr( cols&#91;1&#93; ) ^ 2 + Expr( cols&#91;2&#93; ) ^ 2 ) ) ) );
 
Eval( Eval Expr( newColExpr ) );

Re: JSL: Creating formula using a variable column name

Fri, 20 Nov 2009 22:37:14 GMT

Thanks for the help. Will try it out.
=)

Re: JSL: Creating formula using a variable column name

Mon, 23 Nov 2009 15:14:14 GMT

@mpb: That works fine! Thanks for finding out and for the book tip!