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

Add multiple columns by scripts

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 ),
);
1 ACCEPTED SOLUTION

Accepted Solutions
vince_faller
Super User (Alumni)

Re: Add multiple columns by scripts

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

)

 

Vince Faller - Predictum

View solution in original post

4 REPLIES 4
Thierry_S
Super User

Re: Add multiple columns by scripts

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.
Thierry R. Sornasse
Adam_Xu
Level III

Re: Add multiple columns by scripts

Thanks Thierry! Then how to address this issue?
vince_faller
Super User (Alumni)

Re: Add multiple columns by scripts

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

)

 

Vince Faller - Predictum
Adam_Xu
Level III

Re: Add multiple columns by scripts

Thank you so much, Vince! It works.