nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

I want to use a value in a numeric column to create a reference to the same column number

SpannerHead

I generated a column with integers that I now want to use to reference values in columns of the same number. Column 9 contains the number indicating which column should be referenced by a formula in Column 10. Below are several things that don't work.

Accepted answers

hogi

Which error message is displayed when you hover over the code with the red underline?

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
New Column( "Column 9",
	Numeric,
	Set Values( [1, 2, 3, 4, 5] )
);

New Column( "Column 10",
	Expression,
	Formula( Try( Column( :Column 9 ) ) )
)

no error:

If you don't want to retrieve the columns - but the value stored in the columns, use As column() instead of column():

robust column reference

... or add a row index to Column():
Column(col)[row()] or Column(col)[]

New Column( "Column 10b",
	Character,
	Formula( Try( Char( Column( :Column 9 )[Row()] ) ) )
)

Besides that,

please ensure that the calculated value matches the modelling type of the column.
Columns 1 to 5 contain numbers and characters. This is why I added Char().
Column(.) will fail and the column formula will terminate. So please check if there are missing indices in Column 9

All comments

hogi

Which error message is displayed when you hover over the code with the red underline?

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
New Column( "Column 9",
	Numeric,
	Set Values( [1, 2, 3, 4, 5] )
);

New Column( "Column 10",
	Expression,
	Formula( Try( Column( :Column 9 ) ) )
)

no error:

If you don't want to retrieve the columns - but the value stored in the columns, use As column() instead of column():

robust column reference

... or add a row index to Column():
Column(col)[row()] or Column(col)[]

New Column( "Column 10b",
	Character,
	Formula( Try( Char( Column( :Column 9 )[Row()] ) ) )
)

Besides that,

please ensure that the calculated value matches the modelling type of the column.
Columns 1 to 5 contain numbers and characters. This is why I added Char().
Column(.) will fail and the column formula will terminate. So please check if there are missing indices in Column 9

SpannerHead

hogi

Thanks but those just give me the value already in Column 9, I want to look up the value of a different column as enumerated in Column 9 if that makes sense?

hogi

combining both approaches, let's retrieve the columns in the first step - and the values in the second step:

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
New Column( "column",
	Expression,
	Formula( Try( Column( Row() ), . ) ),
);
New Column( "value",
	Character,
	Formula( Try( Char( :column[Row()][Row()] ), "" ) ),
)

why :column[Row()][Row()]?

:column is column #9,
:column[row()] is the entry in the specific row of this column, e.g. Column ("sex")
and :column[row()][row()] is the entry in the specific row of e.g. Column ("sex")

SpannerHead

hogi

Great addition! No good deed and all that.............

I now need to iteratively operate on every column relative to the maximum value stored in Column 9. The normal addition of the value of i doesn't naturally apply in this case.

clear symbols();

lim = Col Max(Column(8));

For (i=1, i<=lim, i++,

New Column( "Column 10b",
	Character,
	Formula( Try( Char( Column( :Column 9 )[Row()] ) ) )
));

hogi

@SpannerHead wrote:
The normal addition of the value of i doesn't naturally apply in this case.

A very common issue:
a variable defined outside of the new column message - and used inside the new column message:

-> Expression Handling in JMP: Tips and Trapdoors

The solution: look up the variable and use the stored value:

For each  ({i}, 1::5,
Eval(Eval Expr(New Column( "col_"||char(i),
	Character,
	Formula( Try( Char( Column( Expr(i) )[Row()] ) ) )
))));

more robust:

For each  ({i}, 1::5,
Eval(Eval Expr(New Column( "col_"||char(i),
	Character,
	Formula( Try( Char( Expr(Name Expr(As Column( i )) )) ) )
))));

From the first day I saw it in one of @jthi's posts, I admired the Name Expr(As Column(col)) and used it wherever I could.

With the learning from https://community.jmp.com/t5/Discussions/Using-numeric-objects-in-a-column-equation/m-p/903966/highlight/true#M106273 , I am really happy that this approach has helped me avoid many pitfalls.

As Column(col) or Column(col) in a column formula? Too fragile!