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

- JMP User Community
- :
- Discussions
- :
- Formula Loop - variable columns

- 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

Jan 15, 2019 5:33 AM
(3007 views)

Hi,

I have a problem with the script.

I get an empty column when I try to use the loop.

When I don't use a loop, it's works.

```
col = dt << get column names;
For(i = 1, i <= N Items(col), i++,
c = "c" || Char(i);
Eval(Parse(Eval Insert("^c^= column(dt,i)<<get values"))));
```

New Column( "Flag",

Character,

Nominal,

Formula(for(i=1,i<3,i++,

if(c2[i]>:LSL[i],"Pass","Failed"))));

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

First thing I'd say is that this is an XY Problem. But to answer the question of how do you make a flag check a dynamic number of columns, I would build the formula outside of the formula itself.

```
names default to here(1);
dt = New Table( "Test",
Add Rows( 5 ),
Set Header Height( 46 ),
New Column( "System",
Character,
"Nominal",
Set Values( {"F_T_F_S", "F_T_F_R", "F_T_F_T", "F_T_F_U", "F_T_F_V"} )
),
New Column( "01/09/2019",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [500, 450, 900, 358, 500] )
),
New Column( "01/10/2019",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [666, 120, 280, 852, 456] )
),
New Column( "01/11/2019",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [520, 500, 450, 520, 520] )
),
New Column( "01/12/2019",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [450, 666, 120, 450, 450] )
),
New Column( "LSL",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [500, 800, 800, 0, 0] )
),
New Column( "USL",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [980, 1100, 1100, 3700, 3700] )
)
);
//build the formula outside of the formula itself
ANDExpr = Expr(AND()); // we're going to throw all relavent columns into this AND Expression
for(i=2, i<=5, i++,
col = Column(i);
insert into(ANDExpr,
EvalExpr(:LSL < Expr(col)[] < :USL) // for each column check that it's between LSL & USL
);
);
f = EvalExpr(
if(Expr(nameexpr(ANDExpr)),
"Pass",
"Fail"
)
);
show(nameexpr(f));
Eval(EvalExpr(dt << New Column("Flag", formula(Expr(nameexpr(f)))) ));
```

You could also do a list of names instead of numbers.

Vince Faller - Predictum

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: Formula Loop - variable columns

I suspect that your pass/failed is not getting returned as the last calculated value. The following adjustment may solve your issue.

```
col = dt << get column names;
For( i = 1, i <= N Items( col ), i++,
c = "c" || Char( i );
Eval( Parse( Eval Insert( "^c^= column(dt,i)<<get values" ) ) );
);
New Column( "Flag",
Character,
Nominal,
Formula(
For( i = 1, i < 3, i++,
If( c2[i] > :LSL[i],
x = "Pass",
x = "Failed"
)
);
x;
)
);
```

However, you have a bigger issue. Your formula relys on having a memory variable called "c2" for it to work. If you ever change, or delete the value of that variable, the formula will use the changed value to calculate the formula, or if it has been deleted, the formula will no longer work at all.

Jim

Highlighted
##

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

Re: Formula Loop - variable columns

Thx for your answer.

* According to your script, it's not working so well - I get the same answer every row, because you are printing after the loop.

* As for the big issue -

What do you propose to do?

I need to check that every row(to all columns) between the LSL and USL - But the problem is, the name of the columns changes all the time.

Highlighted

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

First thing I'd say is that this is an XY Problem. But to answer the question of how do you make a flag check a dynamic number of columns, I would build the formula outside of the formula itself.

```
names default to here(1);
dt = New Table( "Test",
Add Rows( 5 ),
Set Header Height( 46 ),
New Column( "System",
Character,
"Nominal",
Set Values( {"F_T_F_S", "F_T_F_R", "F_T_F_T", "F_T_F_U", "F_T_F_V"} )
),
New Column( "01/09/2019",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [500, 450, 900, 358, 500] )
),
New Column( "01/10/2019",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [666, 120, 280, 852, 456] )
),
New Column( "01/11/2019",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [520, 500, 450, 520, 520] )
),
New Column( "01/12/2019",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [450, 666, 120, 450, 450] )
),
New Column( "LSL",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [500, 800, 800, 0, 0] )
),
New Column( "USL",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [980, 1100, 1100, 3700, 3700] )
)
);
//build the formula outside of the formula itself
ANDExpr = Expr(AND()); // we're going to throw all relavent columns into this AND Expression
for(i=2, i<=5, i++,
col = Column(i);
insert into(ANDExpr,
EvalExpr(:LSL < Expr(col)[] < :USL) // for each column check that it's between LSL & USL
);
);
f = EvalExpr(
if(Expr(nameexpr(ANDExpr)),
"Pass",
"Fail"
)
);
show(nameexpr(f));
Eval(EvalExpr(dt << New Column("Flag", formula(Expr(nameexpr(f)))) ));
```

You could also do a list of names instead of numbers.

Vince Faller - Predictum

Highlighted
##

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

Re: Formula Loop - variable columns

Great,

Thx for your support.

Next time I will provide full details and solutions I have already tried.

Thx for your support.

Next time I will provide full details and solutions I have already tried.

Article Labels

There are no labels assigned to this post.