topic Re: How to script an aggregate column from a variable list of column references in Discussions

How to script an aggregate column from a variable list of column references

jconte — Fri, 09 Jun 2023 17:07:44 GMT

I have a list of columns that I need to aggregate automatically. Currently, what I'm trying is something like this, which isn't yielding any results.

cols = {:col1, :col2, :col3}

Eval(Eval Expr(
  dt << New Column(
    Formula(Minimum(Expr(cols))
  )
)

I've also tried generating this interactively and saving the column formula. It works, but gives me

Minimum(:col1, :col2, :col3)

which I'm not sure how to re-create in JSL.

Re: How to script an aggregate column from a variable list of column references

jthi — Tue, 16 Aug 2022 17:57:56 GMT

This might give some ideas

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");

cols = {:height, :weight};

// this should work, but formula looks "weird" due to brackets {}
Eval(Eval Expr(
	col1 = dt << New Column("with_brackets", Numeric, Continuous, Formula(Minimum(Expr(cols))))
));

min_f = Substitute(cols, Expr(List()), Expr(Minimum()));
Eval(Eval Expr(
	col2 = dt << New Column("no_brackets", Numeric, Continuous, Formula(Expr(NameExpr(min_f))))
));

show(col1 << get formula, col2 << get formula);

Re: How to script an aggregate column from a variable list of column references

Thierry_S — Tue, 16 Aug 2022 18:02:11 GMT

Hi,

Try this syntax:

Names default to Here (1);

dt = Current Data Table ();

cols = {:col1, :col2, :col3};

dt << New Column("TEST", numerical, continuous, << Formula(Minimum(Eval(cols))));

Best,

Re: How to script an aggregate column from a variable list of column references

jconte — Tue, 16 Aug 2022 18:17:27 GMT

Thanks for the quick response! I tried this, but it seems to leave the column formula unresolved (as Minimum(Eval(cols)) ).

Re: How to script an aggregate column from a variable list of column references

txnelson — Tue, 16 Aug 2022 18:31:52 GMT

Please check your list definition and make sure the ":" is specified in front of each column name.

What version of JMP are you using?

Re: How to script an aggregate column from a variable list of column references

jconte — Tue, 16 Aug 2022 20:00:07 GMT

My actual list is defined through regex matching based on user input. The script iterates through the data table's list of column names and inserts matching ones into a list.

For Each ({col}, colNames,
  If (condition... Insert Into(list, :Column(col))
)

I tried changing the syntax to :(Column(col)) but that resulted in a list of missing items rather than what I had previously been getting ({Column(col1), Column(col2), ...}). Same goes for As Column(col).

I'm using JMP 16.

Re: How to script an aggregate column from a variable list of column references

txnelson — Tue, 16 Aug 2022 20:40:50 GMT

I am guessing on what your initial column names list looks like, but if it is just an unquoted list o column names, the below script is an example of onw way to process it

Names Default To Here( 1 );
dt = 
// Open Data Table: Big Class.jmp
// → Data Table( "Big Class" )
Open( "/C:/Program Files/SAS/JMPPRO/15/Samples/Data/Big Class.jmp" );

colNames = dt << get column names;
theList = {};
For Each( {col}, colNames,
	Show( col );
	If( As Column( col ) << get data type == "Numeric",
		Insert Into( theList, As Column( col ) << get name )
	);
);

theString = Concat Items( theList, ",:" );

Eval( Parse( "dt << new column(\!"test\!",formula(minimum(:" || thestring || ")));" ) );

Re: How to script an aggregate column from a variable list of column references

ErraticAttack — Tue, 16 Aug 2022 20:47:14 GMT

Here are two methods you might try:

Names Default to Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
cols = dt << Get Column Names( "String" );
col list = {};
For Each( {col}, cols,
	If( Ends With( col, "t" ), Insert Into( col list, As Name( col ) ) )
);
Eval( Eval Expr(
dt << New Column( "NEW_COL", "Numeric", <<Set Formula( Min( Eval List( Expr( col list ) ) ) ) )
) )

Names Default to Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
cols = dt << Get Column Names( "String" );
col list = Expr( Min() );
For Each( {col}, cols,
	If( Ends With( col, "t" ), Insert Into( col list, As Name( col ) ) )
);
Eval( Eval Expr(
dt << New Column( "NEW_COL", "Numeric", <<Set Formula( Expr( Name Expr( col list ) ) ) )
) )