topic Re: accessing current column name in formula for that column in Discussions

accessing current column name in formula for that column

galactus3000 — Fri, 06 Dec 2019 15:01:59 GMT

how do I reference the column name of column containing formula in the formula?

for instance, if column name is "3 bears" i want to fill that column (or any column) with a formula like this:

Num(Word(1,current column name()))

such that the column will be filled with the number 3

and upon renaming the column to "16 dogs" it would fill the column with the number 16

the intended application for this feature is less mundane than this ... the extracted number will be incorporated into a non-simple column formula

Re: accessing current column name in formula for that column

Mark_Bailey — Fri, 06 Dec 2019 15:10:08 GMT

I don't think a column formula will do what you want. You are changing the contents upon which the formula depends and the data type. You have to create a new data column if you want to use a column formula.

You could use a short script to iterate over all the columns or a list of selected columns and then change the attribute and value. The original data column would be transformed or changed.

It is not clear what you want to do overall, but can your non-simple column formula extract the desired number from the dependent column name?

Re: accessing current column name in formula for that column

galactus3000 — Fri, 06 Dec 2019 15:28:43 GMT

the data type of the column in question is numeric and set as such with no changes intended

i want to populate the column with a value which is computed from a value contained in the column name itself, more specifically, the numerical value of the 1st Word in the column name

it could be the case that JMP cannot identify to a formula the column in which the formula exists

if that is the case, this question is a non-starter

Re: accessing current column name in formula for that column

txnelson — Fri, 06 Dec 2019 15:37:31 GMT

A JSL script(not a formula) should be able to do what you want.

Re: accessing current column name in formula for that column

Mark_Bailey — Fri, 06 Dec 2019 15:41:13 GMT

I know what you want to do. I am asking if you really need to do it this way. Instead of populating the entire column with the same value to be used in another formula, the final formula could extract the value, too. Eliminate the need for the intermediate value.

Re: accessing current column name in formula for that column

Mark_Bailey — Fri, 06 Dec 2019 15:43:34 GMT

If each of the columns to be changed is already set to numeric data, then this column formula should work:

Num( Word( 1, name << Get Name ) )

Re: accessing current column name in formula for that column

galactus3000 — Fri, 06 Dec 2019 15:58:43 GMT

Mark,

just tried entering this/your formula on a column named "43 birds"

Num( Word( 1, name << Get Name ) )

and instead of filling column with 43, just received this error

Name Unresolved: name 1 times At rows: 2 Operation: name, name/*###*/

in an error box entitled "Column 43 birds Formula Interrupted"

Re: accessing current column name in formula for that column

vince_faller — Fri, 06 Dec 2019 17:20:36 GMT

Assuming you're not changing the position of the Column, you could use it's position.

Num( Word( 1, Column(1) << Get Name ) )

But I'd agree that you probably don't need to have this intermediary step.

Re: accessing current column name in formula for that column

jules — Fri, 06 Dec 2019 17:21:42 GMT

Hi @galactus3000,

I agree with others that there is probably a more efficient way of solving whatever problem you have than using a self-referencing formula to extract the numeric part of the column name. That said, I thought it was an interesting challenge! There isn't (so far as I know) a way to self-reference the originating column via jsl (the way we can use Row(), for example), but we can take advantage of the fact that jmp will automatically update references to a column in scripts and formulas when you change a column name. This means we can reference the column itself in an absolute way, and when you change the name and rerun formulas (important) jmp will update all the rows.

Specifically, here's the formula I would use:

Num( Word( 1, :Name( "16 dogs" ) << get name ) )

and here is what it looks like upon a change:

I hope this helps!

@jules

Re: accessing current column name in formula for that column

galactus3000 — Fri, 06 Dec 2019 17:39:05 GMT

this is perfect!

thank you!