topic Re: Formula Loop - variable columns in Discussions

Formula Loop - variable columns

Botzal — Tue, 15 Jan 2019 13:33:48 GMT

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"))));

Re: Formula Loop - variable columns

txnelson — Tue, 15 Jan 2019 14:15:45 GMT

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.

Re: Formula Loop - variable columns

Botzal — Tue, 15 Jan 2019 15:15:05 GMT

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.

Re: Formula Loop - variable columns

vince_faller — Tue, 15 Jan 2019 18:24:33 GMT

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.

Re: Formula Loop - variable columns

Botzal — Wed, 16 Jan 2019 07:38:37 GMT

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