Turn on suggestions

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

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Using For Loop to Create New Column

Topic Options

- 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

May 30, 2019 10:16 AM
(643 views)

Hi

I try to create the new column by using for loop

```
names default to here (1);
dt = current data table ();
summarize (PN=by (:Name("~DEVICE TYPE" )));
for (i=1, i <= N items(PN), i++,
new column("Cnt "||PN[i],
formula(If( :Name( "~DEVICE TYPE" ) == PN[i],
If( :SERIAL_NO || :TIME_DATE == Lag( :SERIAL_NO || :TIME_DATE, -1 ),0,1)
))));
```

I can create the new column but cannot get the formula work. The log file show below error message.

Column Cnt TRB100BC-08 Formula Interrupted Subscript Range 1 times At rows: 2 Operation: PN[i], PN[/*###*/i] Formula evaluation errors have been ignored

1 ACCEPTED SOLUTION

Accepted Solutions

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

The main issue you have is that the Formula element within the New Column() function does not evaluate before processing, therefore, it will place into the formula your specification PN[i], and since the variable "i" is changing when your loop changes the formula for each of the columns you are creating will change. So what you have to do, is to change the formula to be static values. That is, you do not want the installed formula to have PN[i] but rather the actual evaluated value of whatever PN[i] is. The Substitution() function allows for this to happen. I think the below script will do what you want

```
Names Default To Here( 1 );
dt = Current Data Table();
Summarize( PN = by( :Name( "~DEVICE TYPE" ) ) );
PN={"X"}
For( i = 1, i <= N Items( PN ), i++,
Eval(
Substitute(
Expr(
New Column( __name__,
formula(
If( :Name( "~DEVICE TYPE" ) == __PN__,
If(
:SERIAL_NO || :TIME_DATE ==
Lag( :SERIAL_NO || :TIME_DATE, -1 ),
0,
1
)
)
)
)
),
Expr( __name__ ), "Cnt " || PN[i],
Expr( __PN__ ), PN[i]
)
)
);
```

Jim

2 REPLIES 2

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

The main issue you have is that the Formula element within the New Column() function does not evaluate before processing, therefore, it will place into the formula your specification PN[i], and since the variable "i" is changing when your loop changes the formula for each of the columns you are creating will change. So what you have to do, is to change the formula to be static values. That is, you do not want the installed formula to have PN[i] but rather the actual evaluated value of whatever PN[i] is. The Substitution() function allows for this to happen. I think the below script will do what you want

```
Names Default To Here( 1 );
dt = Current Data Table();
Summarize( PN = by( :Name( "~DEVICE TYPE" ) ) );
PN={"X"}
For( i = 1, i <= N Items( PN ), i++,
Eval(
Substitute(
Expr(
New Column( __name__,
formula(
If( :Name( "~DEVICE TYPE" ) == __PN__,
If(
:SERIAL_NO || :TIME_DATE ==
Lag( :SERIAL_NO || :TIME_DATE, -1 ),
0,
1
)
)
)
)
),
Expr( __name__ ), "Cnt " || PN[i],
Expr( __PN__ ), PN[i]
)
)
);
```

Jim

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

Re: Using For Loop to Create New Column

thank you