Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
Zihao
Level II

How to use Column index (instead of Column name) in a formula.

Hi,

I am trying to do this in a for loop:

In current table, I have 14 Columns. Column #3~#14 were named as "Step 1" to "Step 12".

In the for loop, I want to create Column #15 (named as "Delta Step i" for i = 2, i <=12, i++), which is calculated with formula (Col(4) -Col(2)). Then Column#3 was deleted. The same formula will be reused in the next iteration.

 

Two problems here,

1) my formula doesn't work as there is no value in Column#15 of the output table;

2)I am worried that once column3 is detected, the values in Column15 (actually Column 14, after Column 3 is deleted) will be gone.

 

Here is my script.

For(i=2,i<=15,i++,
	DeltaStep = "Delta Step "||Char(i);	
	New Column(DeltaStep)<<formula(Column(4) - Column(3));
	Column(3)<<delete columns
);

 

I also tried this, but not working, either.

For(i=2,i<=12,i++,
	DeltaStep = "Delta Step "||Char(i);
	Pre = Column(3) << getname;
	Pst = Column(4) << getname;
	New Column(DeltaStep)<<formula(:Pst - :Pre);
	Column(3)<<delete columns
);

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Zihao
Level II

Re: How to use Column index (instead of Column name) in a formula.

I am also wrong for the "delete columns" expr. 

Attached the correct script here, please let me know if any better way to do this. 

Thanks!

For(i=2,i<=12,i++,
	DeltaStep = "Delta Step "||Char(i);
	New Column(DeltaStep,formula(Column(4)[] - Column(3)[]));
	Column(3) << Set Selected;
	dt << Delete Columns
);

View solution in original post

3 REPLIES 3
Highlighted
Zihao
Level II

Re: How to use Column index (instead of Column name) in a formula.

I think I found why I am not doing this correctly...

 

For the first script in my post, I should add [] after Column(3)...

 

Thanks,

Zihao

Highlighted
Zihao
Level II

Re: How to use Column index (instead of Column name) in a formula.

I am also wrong for the "delete columns" expr. 

Attached the correct script here, please let me know if any better way to do this. 

Thanks!

For(i=2,i<=12,i++,
	DeltaStep = "Delta Step "||Char(i);
	New Column(DeltaStep,formula(Column(4)[] - Column(3)[]));
	Column(3) << Set Selected;
	dt << Delete Columns
);

View solution in original post

Highlighted
gzmorgan0
Super User

Re: How to use Column index (instead of Column name) in a formula.

Good catch! People often forget that Column(#) or Column("name")  need the empty brackets ( [] )  or the As Column() wrapper for row-wise calculations. I like to think of the reference as a pointer to the column header and attributes and the ref[] exposes the vector or list of values.  

 

Regarding delete columns, it is a table message.  You could have alo used the JSL below without selecting the column beforehand.

dt << Delete Columns( 3 );

Another common mistake is trying to delete a column that is used as an argument for another column formula.  Suppose Col 4 uses a formula that references Col 3. You must remove the dependent formula in Col 4, then you can delete Col 3.

 

This is another reason for using << Set Each Value(expression) vs. Formula (expression) for most cases, then the new columns are not locked, and dependencies do not exist.

 

 

Article Labels

    There are no labels assigned to this post.