topic Re: Add multiple columns by scripts in Discussions

Add multiple columns by scripts

Adam_Xu — Wed, 17 Jul 2019 14:06:56 GMT

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

Re: Add multiple columns by scripts

Thierry_S — Wed, 17 Jul 2019 14:22:37 GMT

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.

Re: Add multiple columns by scripts

Adam_Xu — Wed, 17 Jul 2019 14:48:04 GMT

Thanks Thierry! Then how to address this issue?

Re: Add multiple columns by scripts

vince_faller — Wed, 17 Jul 2019 15:25:56 GMT

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

)

Re: Add multiple columns by scripts

Adam_Xu — Thu, 18 Jul 2019 01:36:36 GMT

Thank you so much, Vince! It works.