Having a column formula update based on a associative array referenced by a list.

JMewborn — Sat, 10 Jun 2023 23:49:31 GMT

I have a column formula that tests a product that can either pass or fail based on specification limits. An associative array is used to reference a low and high limit of different specs of the product. The issue I am having is that the user selects the product specs they want to import into the pass/fail column. They are different every time, after referenced from a different data table. The goal is to have the formula column iterate through the list of requested specs, checking if each one is inside the limits. If so, an overall pass/fail classification will be attributed. I would have this done outside the formula column, but a later part of the script is going to be refreshing that formula column repeatedly to continue analysis of passing and failing by changing the spec limit tolerances. I believe the entire calculation would need to be in the column formula itself

I have attached two data tables, and the work in progress script below.

Names Default To Here(1);



//declare associative array for specs
specs = Associative Array();/*
//input limits file
LimitsCSV = Pick File(
	"Select The Appropriate Spec Limit Table",
	 "filepath"//Insert file path here.
	
);
//open limits file in JMP
LimitsTable = open( LimitsCSV, invisible);




//Open Current Product Testing Data
DataTable = Pick File(
	"Select The Current Product Testing Data",
	"filepath"// Insert File Path Here
	
);
//set testing data to be able to manipulate in JMP
GraphingTable = open(DataTable);

//Select the Specs you want to compare by using a COL LIST BOX
ListNumericCols = GraphingTable << Get Column Names(numeric);
New Window("Select Wanted Specifications", << Modal, 
	clb = Col List Box (<< Set Items (ListNumericCols))
);

ListSpecNames = clb << Get Selected;
show(ListSpecNames); //Display for testing

//insert spec names into associative array
for (i=1,i< (N Items(ListSpecNames) + 1), i++,
	
	Insert Into(specs, ListSpecNames[i]);
	ColSpec = Column(LimitsTable, 1); //1 is the coolumn for spec name
	
	LimitList= {};
	
	//Insert Lower Limit onto 
	for(j=1, j < (nrows(LimitsTable)+1), j++,
	
		if (ColSpec[j] == ListSpecNames[i],
		ColLimit = Column(LimitsTable, 2);//14 is the column for lower limit
		MinVal = 0;
		if(ColLimit[j] > 0, MinVal = ColLimit[j] / 2, MinVal = ColLimit[j]*2);
		InsertInto(LimitList, MinVal );
		break();
	));
	for(k=1, k < (nrows(LimitsTable)+1), k++,
	
		if (ColSpec[k] == ListSpecNames[i],
		ColLimit = Column(LimitsTable, 2);//14 is the column for lower limit
		InsertInto(LimitList, ColLimit[k]);
		break();
	));
	for(l=1, l < (nrows(LimitsTable)+1), l++,
	
		if (ColSpec[l] == ListSpecNames[i],
		ColLimit = Column(LimitsTable, 3);//14 is the column for lower limit
		InsertInto(LimitList, ColLimit[l]);
		break();
	));
	for(m = 1, m < (nrows(LimitsTable)+1), m++,
		if (ColSpec[m] == ListSpecNames[i],
		ColLimit = Column(LimitsTable, 3);//14 is the column for upper limit
		MaxVal = 0;
		if(ColLimit[m] > 0, MaxVal = ColLimit[m] *2, MaxVal = ColLimit[m] /2 );
		InsertInto(LimitList, MaxVal );
		break();
		)	
	);	
	
	
	specs[ListSpecNames[i]] = LimitList
);

close(LimitsTable, no save);
PassBool = 1;
ColNameList = GraphingTable << get column names(string);
newcolumn = "Pass/Fail";
if (!contains(ColNameList, NewColumn),
GraphingTable << new column("Pass/Fail"));/*
if(((column("m1") >= specs["m1"][2]) & (column("m2") <= specs["m2"][3])),
		show("Pass"),
		show("Fail")
	);*/
Column(GraphingTable, "Pass/Fail") << Set Formula(
	//counter = 0;
	
	for(i=1,i< (N Items(ListSpecNames) + 1), i++,
			
		if((Column(ListSpecNames[i]) >= specs[ListSpecNames[i]][2]) & (Column(ListSpecNames[i]) <= specs[ListSpecNames[i]][3]),
		show("Pass"),
		show("Fail");
		PassBool = 0;
		break();
		)
	) ;
	If(PassBool == 1, "Pass","Fail")
		
		
	);
	


show(specs);

and my jsl script below.

Re: Having a column formula update based on a associative array referenced by a list.

jthi — Mon, 13 Jun 2022 19:55:44 GMT

I would most likely build the formula as a string and then evaluate into the column (see )

This isn't the most simple script to understand but run it in parts and add debug show() here and there to understand it better.

Names Default To Here(1);

//open limits file in JMP
LimitsTable = open("vinyl data limits.jmp", invisible);
//set testing data to be able to manipulate in JMP
GraphingTable = open("Vinyl Data.jmp");

//Select the Specs you want to compare by using a COL LIST BOX
ListNumericCols = GraphingTable << Get Column Names(numeric);
nw = New Window("Select Wanted Specifications", << Modal, << return result,
	clb = Col List Box (<< Set Items (ListNumericCols))
);

ListSpecNames = nw["clb"];

// https://www.jmp.com/support/help/en/16.2/#page/jmp/associative-arrays-in-set-operations.shtml
specs = Associative Array(LimitsTable[0, 1],
	AsList(LimitsTable[0, 2::3])
);
close(LimitsTable, no save);
sel_specs = Associative Array(ListSpecNames);
specs << Intersect(sel_specs);

if(!contains(GraphingTable << Get Column Names("String"), "Pass/Fail"),
	GraphingTable << new column("Pass/Fail", Character);
);

// as we have to have a formula and we have to build it dynamically, we will build it as expression
// which is based on specs associative array
// with JMP16 we can use For Each, otherwise we would have to use For
// I dont like using eval parse but in this case it is most likely easiest to build formula as string
f = "If(All(";
For Each({{spec, limits}}, specs,
	f ||= Eval Insert("^limits[1]^ < :^spec^ < ^limits[2]^ & ");
);
//drop last &
f = Left(f, Length(f) - 3);
f ||= "),"; // close all
f ||= "\!N\!t\!"PASS\!",\!"FAIL\!"
)";

Eval(EvalExpr(Column(GraphingTable, "Pass/Fail") << Set Formula(
	Expr(Parse(f));
)));

You could also think about using Spec Limits column properties depending on what you are doing.

topic Having a column formula update based on a associative array referenced by a list. in Discussions

Having a column formula update based on a associative array referenced by a list.

Re: Having a column formula update based on a associative array referenced by a list.