Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- Referencing a Column by Column Number in Formula in a For loop

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 19, 2018 3:34 PM
(4858 views)

Hi all,

I am trying to generate a bunch of new columns at once using a "for" loop, and each column will get named as the next item in the "sample" matrix. I can figure that part out, I think. But I also want each column to include a formula to look up a different column, which will be different each time, and subtract that value from the initial value (column "0"). When I run this it just gives me formula evaluation errors, not calculating what the column should be that it references.

Thanks for your help!

```
sample = {0, 1, 2, ...}
new columns = N Items (sample);
for( i=1, i<= new columns, i++, col = num(sample [i]);
dt << New Column (sample [i] || " Days", formula (as column (13+i+new columns) - :Name("0")))
);
```

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

My error. The string needs to be parsed to get rid of the pair of quotes

```
Names Default To Here( 1 );
dt = Current Data Table();
sample = {0, 1, 2};
new columns = N Items( sample );
For( i = 1, i <= new columns, i++,
col = Num( sample[i] );
colNamesList = dt << Get Column Names;
Wait( 0 );
Eval(
Substitute(
Expr(
dt << New Column( sample[i] || " Days",
formula( __column__ - :Name( "0" ) )
)
),
Expr( __column__ ),
Parse(":Name(\!"" || colNamesList[13 + i + new columns] || "\!")")
)
);
);
```

Jim

4 REPLIES 4

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Referencing a Column by Column Number in Formula in a For loop

The issue with your current formula, is the variables "i".and "New Columns". What you are assuming is that the calculation will be evalueated to a final calculation, and then placed into the As Column() function. The issue is that what is placed into the formula is As Column( 13 + i + new columns ). So when the formula is executed, it does not have the values of "i" and "new columns" when you created the column, but it uses whatever the values are whenever it calculates or recalculates the formulas. And since "i" and "New Columns" is changing within your script, you have an issue. Now you could change the formula to do the calculation before defining the formula, and then substitute in the column number into the formula. However, the issue with this is that by using column numbers, if a column in the table is moved, or deleted, the column number in the formula will not be changed and therefore, the calculation will be wrong. I suggest that you instead, substitute into the formula, the actual column name. The below script illustrates the approach to do that.

```
Names Default To Here( 1 );
dt = Current Data Table();
sample = {0, 1, 2};
new columns = N Items( sample );
For( i = 1, i <= new columns, i++,
col = Num( sample[i] );
colNamesList = dt << Get Column Names;
Wait( 0 );
Eval(
Substitute(
Expr(
dt << New Column( sample[i] || " Days",
formula( __column__ - :Name( "0" ) )
)
),
Expr( __column__ ),
":Name(\!"" || colNamesList[13 + i + new columns] || "\!")"
)
);
);
```

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Referencing a Column by Column Number in Formula in a For loop

Thank you for your help!! This solution almost works - but I am stuck on one last bit.

Currently, the resulting spreadsheet gives me the following formula.

`":Name(\!"5\!")" - 0`

I want the first half to reference a column called "5", like the second half references a column called "0". No matter what I do, I can't get the first half to just show :Name("5"). When I reformat the quotation marks or try moving them, I just get variations on that theme.

Thanks again!

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

My error. The string needs to be parsed to get rid of the pair of quotes

```
Names Default To Here( 1 );
dt = Current Data Table();
sample = {0, 1, 2};
new columns = N Items( sample );
For( i = 1, i <= new columns, i++,
col = Num( sample[i] );
colNamesList = dt << Get Column Names;
Wait( 0 );
Eval(
Substitute(
Expr(
dt << New Column( sample[i] || " Days",
formula( __column__ - :Name( "0" ) )
)
),
Expr( __column__ ),
Parse(":Name(\!"" || colNamesList[13 + i + new columns] || "\!")")
)
);
);
```

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Referencing a Column by Column Number in Formula in a For loop

Thank you so much!!

Article Labels

There are no labels assigned to this post.