cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Lharbicht
Level III

Creating new columns and inserting formula based on the original column cells

Hello,

 

I'm an inexperienced JMP scriptor and I'm trying to write my first larg(er) script. I'm having trouble with one part. Here's what I'm trying to do: I have various data tables that I will use this script on. I would like the script to look at all of the columns, excluding the first column (column and row numbers will vary for each data table) and create a new column for each column already present in the table. I would like the name of each of the new columns to be the same as each of the original columns but with a "_code" at the end, and preferably be positioned next to the original column in the table. Within each new column I would like the formula to be an if/then statement. If "Column A" has the text "<" in the cell, then 0, otherwise 1.

 

I hope my explanation makes sense. I've posted my script although it seems to break at line 8 and I'm not sure why. 

 

Thank you for any help!

 

Lacey

 

 

Names Default To Here( 1 );

dt = Current Data Table();

col_list = dt << get column names( string );

For( i = 2, i <= nitems( col_list ), i++,
	
		data_col = ":" || col_list[i];
		
		code_col = col_list[i] || "_Code";
		
		str = evalinsert (
		
			"\[dt << New Colum ("^code_col^", Numeric, Continuous,
			Format("Best",12),
			Formula(If( Contains( "^code_col^", "<" ),
	0,
	1
)));
				
			);]\");
			
			eval(parse(str));
);
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Creating new columns and inserting formula based on the original column cells

Here is a modification of your code that I believe meets all of your needs.  One item that I had to deal with, is that the columns that you start with (2-13) have to be character, so the formula does not error out.  The Column 13 does error out the script, since it is a formula column that can not be changed to character, because as soon as it runs it's formula, it will force the column to be numeric.

Names Default To Here( 1 );

dt = Current Data Table();

col_list = dt << get column names( string );

For( i = 13, i <= N Items( col_list ), i++,
	Eval(
		Substitute(
				Expr(
					Column(col_list[i])  << Data Type( Character );
					dt << Add Multiple Columns( __NewColName__, 1, after( __OrigCol__ ), Numeric );
					Column( __NewColName__ ) << set formula( If( Contains( __OrigCol__, "<" ), 1, 0 ) );
				),
			Expr( __NewColName__ ), col_list[i] || "_Code",
			Expr( __OrigCol__ ), Parse( ":" || col_list[i]  )
		)
	)
	
);
Jim

View solution in original post

5 REPLIES 5
txnelson
Super User

Re: Creating new columns and inserting formula based on the original column cells

Here is a modification of your code that I believe meets all of your needs.  One item that I had to deal with, is that the columns that you start with (2-13) have to be character, so the formula does not error out.  The Column 13 does error out the script, since it is a formula column that can not be changed to character, because as soon as it runs it's formula, it will force the column to be numeric.

Names Default To Here( 1 );

dt = Current Data Table();

col_list = dt << get column names( string );

For( i = 13, i <= N Items( col_list ), i++,
	Eval(
		Substitute(
				Expr(
					Column(col_list[i])  << Data Type( Character );
					dt << Add Multiple Columns( __NewColName__, 1, after( __OrigCol__ ), Numeric );
					Column( __NewColName__ ) << set formula( If( Contains( __OrigCol__, "<" ), 1, 0 ) );
				),
			Expr( __NewColName__ ), col_list[i] || "_Code",
			Expr( __OrigCol__ ), Parse( ":" || col_list[i]  )
		)
	)
	
);
Jim
Lharbicht
Level III

Re: Creating new columns and inserting formula based on the original column cells

That worked great! Thanks! 

 

It was my mistake that I left that "column 13" in the datatable. That was just me making sure my if then statement was working and I forgot to delete it when I posted it online. So I just deleted that out of my datatable, changed the 13 to a 2 in the wonderful script you sent and then it worked like a charm.


Thank you so much!

 

Lacey

Lharbicht
Level III

Re: Creating new columns and inserting formula based on the original column cells

Hi Jim,

 

I went to try the script on a new data table and I ran into a problem with my column names. I deal with environmental data, and many times the analyte names (ie. column names) will have symbols in the name such as "-", "[", "]", "(", ")", and/or ",". When I ran the script it errored on the first column which was "3-Methyl-1-butene".  Is there a fix for this?


Thank you,

 

Lacey

txnelson
Super User

Re: Creating new columns and inserting formula based on the original column cells

The :Name() function is designed to handle this issue.  Below is a modification I made to your script that I believe will solve the issue.  I have not tested this modification, but I think it will work.  If not, you should be able to see what I am attempting to do, and you should be able to get it to work.

Names Default To Here( 1 );

dt = Current Data Table();

col_list = dt << get column names( string );

For( i = 13, i <= N Items( col_list ), i++,
	Eval(
		Substitute(
				Expr(
					Column(col_list[i])  << Data Type( Character );
					dt << Add Multiple Columns( __NewColName__, 1, after( __OrigCol__ ), Numeric );
					Column( __NewColName__ ) << set formula( If( Contains( __OrigCol__, "<" ), 1, 0 ) );
				),
			Expr( __NewColName__ ), col_list[i] || "_Code" ,
			Expr( __OrigCol__ ), Parse( ":Name(\!"" || col_list[i] || "\!:)" )
		)
	)
	
);
Jim
Lharbicht
Level III

Re: Creating new columns and inserting formula based on the original column cells

Yes, I got that to work. Thank you so much!