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

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

Zihao — Wed, 19 Sep 2018 16:54:48 GMT

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

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

Zihao — Wed, 19 Sep 2018 17:37:25 GMT

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

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

Zihao — Wed, 19 Sep 2018 17:45:30 GMT

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

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

gzmorgan0 — Wed, 19 Sep 2018 19:09:05 GMT

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.