topic Re: I want to use a value in a numeric column to create a reference to the same column number in Discussions

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

SpannerHead — Tue, 30 Sep 2025 19:53:26 GMT

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.

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

hogi — Tue, 30 Sep 2025 20:59:19 GMT

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():

... 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

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

SpannerHead — Tue, 30 Sep 2025 20:52:06 GMT

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?

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

hogi — Tue, 30 Sep 2025 21:09:44 GMT

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")

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

SpannerHead — Tue, 30 Sep 2025 22:09:12 GMT

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

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

hogi — Wed, 01 Oct 2025 07:50:29 GMT

@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:

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!