Add multiple columns by scripts

[#1]

Hello, 

I wonder to add multiple columns on the defined data table. If column has already existed, then the script could ignore the column and continue to add the rest ones if not existed until the implemented.

I worte a script here. But the issue is even the column existed, the script still add duplicated column in the data table. Could you help to address this issue? Appreciate it!

Names Default To Here( 1 );
dt0 = Current Data Table();


tnames = dt0 << Get Column Names;
n = N Items( tnames );


For( i = 1, i <= n, i++,
	If( (tnames[i] ==  Char("CpK<1.0") ),
		judA = 0,
		judA=1
	);
	If( (tnames[i] == Char("1.0<=CpK <1.33") ),
		judB = 0,
		judA=1
	);
	If( (tnames[i] == Char("1.33<=CpK<1.67") ),
		judC = 0,
		judC=1
	);
	If( (tnames[i] ==  Char("1.67<=CpK<2.0") ),
		judD = 0,
		judD=1
	);
	If( (tnames[i] == Char("CpK>=2.0") ),
		judE = 0,
		judE=1
	);
);

If( judA == 1,
	dt0 << New Column( "CpK<1.0", numeric, formula( If( :Cpk < 1, 1, 0 ) ), EvalFormula ),
);
If( judB == 1,
	dt0 << New Column( "1.0<=CpK <1.33", numeric, formula( If( 1 <= :Cpk < 1.33, 1, 0 ) ), EvalFormula ),
);
If( judC == 1,
	dt0 << New Column( "1.33<=CpK<1.67", numeric, formula( If( 1.33 <= :Cpk < 1.67, 1, 0 ) ), EvalFormula ),
);
If( judD == 1,
	dt0 << New Column( "1.67<=CpK<2.0", numeric, formula( If( 1.67 <= :Cpk < 2, 1, 0 ) ), EvalFormula ),
);
If( judE == 1,
	dt0 << New Column( "CpK>=2.0", numeric, formula( If( :Cpk >= 2, 1, 0 ) ), EvalFormula ),
);

[#2]

Hi,
The problem is in the loop where your 5 switches judA - judE are reevaluated for each column. So when you have gone through the n existing columns, the state of all your switches are set to the last column.

[#3]

Thanks Thierry! Then how to address this issue?

[#4 · Accepted solution]

You could do something like this. 

Names default to here(1);
dt = open("$SAMPLE_DATA\Big Class.jmp");
dt << New Column("Cpk", Set Each Value(random normal(1, 1)));
dt << New Column("1.0<=CpK <1.33", Set Each Value(0));

col_names = dt << Get column names(string); // this is your existing columns

//I would just do a not contains which checks where an item is in a list, returning 0 if it doesn't
if(!Contains(col_names, "CpK<1.0"), 
	dt << New Column( "CpK<1.0", numeric, 
		formula( 
			// you also don't have to do the if(thing, 1, 0) :Cpk < 1 will resolve to 1 and 0 anyway
			:Cpk < 1
		)
	)
);
// this one shouldn't get remade
if(!Contains(col_names, "1.0<=CpK <1.33"), 
	dt << New Column( "1.0<=CpK <1.33", numeric, formula( 1.0<=:Cpk <1.33 ))
);

if(!Contains(col_names, "1.33<=CpK<1.67"), 
	dt << New Column( "1.33<=CpK<1.67", numeric, formula( 1.33<=:Cpk <1.67 ))
);

if(!Contains(col_names, "1.67<=CpK<2.0"), 
	dt << New Column( "1.67<=CpK<2.0", numeric, formula( 1.67<=:Cpk <2 ))
);

if(!Contains(col_names, "CpK>=2.0"), 
	dt << New Column( "CpK>=2.0", numeric, formula( 2.0<=:Cpk ))
);

You could also abstract it a little so you can change only 1 data structure and it would change your fields.  

//But I would probably do it another way
Names default to here(1);
dt = open("$SAMPLE_DATA\Big Class.jmp");
dt << New Column("Cpk", Set Each Value(random normal(1, 1)));
dt << New Column("1<=Cpk<1.33", Set Each Value(0));
col_names = dt << Get column names(string); // this is your existing columns

//I'd make a 2 col matrix with low and high check then make the columns dynamically
cpk_check = [. 1, 1 1.33, 1.33 1.67, 1.67 2, 2 .];
for(i=1, i<=nrows(cpk_check), i++, 
	low = cpk_check[i, 1];
	high = cpk_check[i, 2];

	if(!ismissing(low), 
		if(!ismissing(high), 
			col_name = char(low) || "<=Cpk<" ||char(high);
			f = EvalExpr(Expr(low)<=:Cpk<Expr(high));
		, //else high is missing
			col_name = char(low) || "<=Cpk";
			f = EvalExpr(Expr(low)<=:Cpk);
		)
	, //else
		if(!ismissing(high), 
			col_name = "Cpk<" ||char(high);
			f = EvalExpr(:Cpk<Expr(high));
		, //else high is missing
			//assuming you don't want this
			throw("high and low values can't both be missing")
		);
	);
	
	show(col_name, nameexpr(f));
	if(!Contains(col_names, col_name), 
		Eval(EvalExpr(dt << New Column( col_name, numeric, formula( Expr(nameexpr(f)) )) ));
	);

)

[#5]

Thank you so much, Vince! It works.