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
Sburel
Level IV

Create a column formula using a column name from a list

Hello,

 

I have a simple script where I want to create a new column in which a formula calculate the absolute value of another column.

 the column is referenced in a list.

I tried a few different ways, but I can't figure out how to evaluate the referenced column so I end up with an empty formula column. Any suggestion would be greatly appreciated.

 

Best

 

Sebastien

dt=New Table( "test",
	Add Rows( 4 ),
	New Column( "symbol",
		Character,
		"Nominal",
		Set Values( {"a", "b", "c", "d"} ),
		Set Display Width( 55 )
	),
	New Column( "fold change",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [-3, -0.4, 0.4, 3] ),
		Set Display Width( 68 )
	)
);

l={"fold change"};

try(New Column( "absolute fold change",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( Abs( l[1] ) 
)));

try(New Column( "absolute fold change 2",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( eval(column(l[1]) ) 
)));

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
lgober
Level II

Re: Create a column formula using a column name from a list

dt=New Table( "test",
	Add Rows( 4 ),
	New Column( "symbol",
		Character,
		"Nominal",
		Set Values( {"a", "b", "c", "d"} ),
		Set Display Width( 55 )
	),
	New Column( "fold change",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [-3, -0.4, 0.4, 3] ),
		Set Display Width( 68 )
	)
);

l = {"fold change"};

try(New Column( "absolute fold change",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( Abs( column( l[1] )[] ) 
)));

Since your list contains only strings, you need to tell JMP that the string is a column name by using the Column( "" )[ ] function.

The blank square brackets are necessary as well as without them, the formula will reference the entire column instead of only the appropriate row.

 

Hope this helped,

 

Lain

View solution in original post

lgober
Level II

Re: Create a column formula using a column name from a list

No problem. I had this exact same issue yesterday and it took me a while to figure it out.

I actually just learned of another solution to this, too.

The 'As Column()' function will return the value of the current row in the column specified as an argument.

So that formula could equally have been formatted like this:

Formula(
Abs( As Column( l[1] ) );
);

Pretty neat!

View solution in original post

3 REPLIES 3
lgober
Level II

Re: Create a column formula using a column name from a list

dt=New Table( "test",
	Add Rows( 4 ),
	New Column( "symbol",
		Character,
		"Nominal",
		Set Values( {"a", "b", "c", "d"} ),
		Set Display Width( 55 )
	),
	New Column( "fold change",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [-3, -0.4, 0.4, 3] ),
		Set Display Width( 68 )
	)
);

l = {"fold change"};

try(New Column( "absolute fold change",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( Abs( column( l[1] )[] ) 
)));

Since your list contains only strings, you need to tell JMP that the string is a column name by using the Column( "" )[ ] function.

The blank square brackets are necessary as well as without them, the formula will reference the entire column instead of only the appropriate row.

 

Hope this helped,

 

Lain

Sburel
Level IV

Re: Create a column formula using a column name from a list

Thanks a lot, I had tried the option you proposed with one big difference: No empty brackets.
Best,

Sebastien
lgober
Level II

Re: Create a column formula using a column name from a list

No problem. I had this exact same issue yesterday and it took me a while to figure it out.

I actually just learned of another solution to this, too.

The 'As Column()' function will return the value of the current row in the column specified as an argument.

So that formula could equally have been formatted like this:

Formula(
Abs( As Column( l[1] ) );
);

Pretty neat!