905189_en-US

liliconcom

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.

 

 

 

 

Automation and ScriptingRe: 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:

 

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

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?

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

 

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

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/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!