cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
  • JMP 19 is here! See the new features at jmp.com/new.
  • Register to attend Discovery Summit 2025 Online: Early Users Edition, Sept. 24-25.
Choose Language Hide Translation Bar
SpannerHead
Level VI

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

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.

 

SpannerHead_0-1759261892873.png

SpannerHead_1-1759261922186.png

SpannerHead_2-1759261949338.png

 

 

 


Slán



SpannerHead
1 ACCEPTED SOLUTION

Accepted Solutions
hogi
Level XIII

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

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:

hogi_3-1759265404323.png

 

hogi_1-1759264810963.png


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

hogi_2-1759265089546.png


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

View solution in original post

5 REPLIES 5
hogi
Level XIII

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

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:

hogi_3-1759265404323.png

 

hogi_1-1759264810963.png


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

hogi_2-1759265089546.png


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
Level VI

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

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?


Slán



SpannerHead
hogi
Level XIII

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

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

hogi_0-1759266391575.png

 

SpannerHead
Level VI

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

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

Slán



SpannerHead
hogi
Level XIII

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

 


@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/highl... , 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!

Recommended Articles