topic Re: Create a column formula using a column name from a list in Discussions

Create a column formula using a column name from a list

Sburel — Fri, 09 Jun 2023 23:32:20 GMT

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]) ) 
)));

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

lgober — Fri, 17 Jul 2020 17:52:48 GMT

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

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

Sburel — Fri, 17 Jul 2020 18:34:49 GMT

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

Sebastien

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

lgober — Fri, 17 Jul 2020 18:52:32 GMT

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!