Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
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
Highlighted
vince_faller
Super User

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
Highlighted
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
Highlighted
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

Highlighted
vince_faller
Super User

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

Highlighted
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.
Article Labels

    There are no labels assigned to this post.