topic use a value to find the correct column to use in a formula in Discussions

use a value to find the correct column to use in a formula

cyrilmichelland — Fri, 09 Jun 2023 15:58:51 GMT

Hi,

I have a summary table where I'd like to create a new column 'Rate' with a formula. The column MAXRESULT gives the name of the column I want to use in the formula. unfortunately, I'm getting 'missing arguments' error when I create the formula based on column name.

Column 'Rate' formula:

(100 * Column Name( Parse( "N(" || Char( :MAXRESULT ) || ")" ) )[]) / :N Rows

What's wrong ?

Re: use a value to find the correct column to use in a formula

jthi — Thu, 22 Sep 2022 15:13:30 GMT

One option is to use As Column() or Column() instead of Column Name() (with As Column() you shouldn't need []). Also there should be no need to use Parse().

Something like this

(100 * As Column("N(" || Char(:MAXRESULT) || ")")) / :N Rows

Re: use a value to find the correct column to use in a formula

cyrilmichelland — Tue, 27 Sep 2022 09:27:09 GMT

Hi,

the proposed formula still doesn't work :(

Error unresolved column

Error Could not find column

anything else I can try out ?

Thanks for your support

Re: use a value to find the correct column to use in a formula

txnelson — Tue, 27 Sep 2022 11:22:41 GMT

Here is an example that shows that @jthi column does work. I believe that you have an issue with your data.

New Table( "Untitled 12",
	Add Rows( 4 ),
	New Column( "N(1.6)",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [445, 446, 0, 0] )
	),
	New Column( "N(1.5)",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [0, 0, 441, 446] )
	),
	New Column( "MAXRESULT",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1.6, 1.6, 1.5, 1.5] )
	),
	New Column( "N Rows",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [446, 446, 446, 446] )
	),
	New Column( "Rate",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula( (100 * As Column( "N(" || Char( :MAXRESULT ) || ")" )) / :N Rows ),
		Set Selected
	)
)

Re: use a value to find the correct column to use in a formula

cyrilmichelland — Tue, 27 Sep 2022 13:54:50 GMT

ok it's working on my data set as well.

the strange thing is it's still showing the error in the preview but then when I apply the formula, the result is good.

Thank you all !

Re: use a value to find the correct column to use in a formula

jthi — Tue, 27 Sep 2022 14:16:00 GMT

Formula editor doesn't like the formula. There are quite a few formulas which can have those red-underlines but they will still work (using Current Data Table() for example). In this case I think it is caused by not evaluation the column names into the formula