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
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
Highlighted
vince_faller
Super User

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
Highlighted
Thierry_S
Level VI

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

Re: Add multiple columns by scripts

Thanks Thierry! Then how to address this issue?
Highlighted
vince_faller
Super User

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

Highlighted
Adam_Xu
Level III

Re: Add multiple columns by scripts

Thank you so much, Vince! It works.

Article Labels

    There are no labels assigned to this post.