cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Botzal
Level III

Formula Loop - variable columns

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
vince_faller
Super User (Alumni)

Re: Formula Loop - variable columns

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

View solution in original post

4 REPLIES 4
txnelson
Super User

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
Botzal
Level III

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.

 

 

 

Capture.PNG

vince_faller
Super User (Alumni)

Re: Formula Loop - variable columns

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
Botzal
Level III

Re: Formula Loop - variable columns

Great,
Thx for your support.
Next time I will provide full details and solutions I have already tried.