I have a table (Limits) with the limits. The table columns are Product, Componet A, Componet B. Under each componet is the limit. Example Product, Aluminum, Zinc
Fudd .24 .50
I have another table FG Metals that has a PASS/FAIL column and a Aluminum column and a Zinc column with the results in each row. Example PASS/FAIL, Aluminum, Zinc
.09 .1
I need to compare the dt(FG metals) Aluminum .09 result with the dt(Limits) Aluminum .24. If result is less than limit then put Pass in the PASS/FAIL column otherwise put FAIL.
Hopefully I explained this well enough. Please let me know if you have any questions.
Wayne
Here is a script that works based upon the example data you supplied.....and with the above changes. The code uses the limits data table to determine what metals to test.
Names Default To Here( 1 );
dtLimits = Data Table( "Limits sample (1)" );
dtMetals = Data Table( "FG Sample (1)" );
// Get the names of the columns with the metals data in them
metalsColNames = dtLimits << get column names( string, numeric );
For( theRow = 1, theRow <= N Rows( dtMetals ), theRow++,
If( N Rows( dtLimits << get rows where( dtLimits:product == dtMetals:Product[theRow] ) ) > 0,
theProductRow = (dtLimits << get rows where( dtLimits:product == dtMetals:Product[theRow] ))[1];
result = "Pass";
For( theColumn = 1, theColumn <= N Items( metalsColNames ), theColumn++,
target = Column( dtLimits, metalsColNames[theColumn] )[theProductRow];
col = Column( dtMetals, metalsColNames[theColumn] );
If( col[theRow] < target,
result = "Fail"
);
);
dtMetals:Name( "Pass/Fail" )[theRow] = result;
)
);
I have attached sample files.
Here is a script that I think does what you want
Names Default To Here( 1 );
// Create example data tables
dtLimits = New Table( "Limits",
New Column( "Product", character, values( {"Fudd"} ) ),
New Column( "Aluminum", values( {.24} ) ),
New Column( "Zinc", values( {.50} ) )
);
dtMetals = New Table( "FG Metals",
add rows( 10 ),
New Column( "Pass/Fail Aluminum", character ),
New Column( "Pass/Fail Zinc", character ),
New Column( "Aluminum", formula( Random Uniform( 0, 1 ) ) ),
New Column( "Zinc", formula( Random Uniform( 0, 1 ) ) )
);
// Get the names of the columns with the metals data in them
metalsColNames = dtMetals << get column names( striing, numeric );
// Loop across the columns and down the rows and calculate the pass/fail
For( theColumn = 1, theColumn <= N Items( metalsColNames ), theColumn++,
target = Column( dtLimits, metalsColNames[theColumn] )[1];
col = Column( dtMetals, metalsColNames[theColumn] );
passFail = Column( dtMetals, " Pass/Fail " || metalsColNames[theColumn] );
For( theRow = 1, theRow <= N Rows( dtMetals ), theRow++,
If( col[theRow] < target,
passFail[theRow] = "Fail",
passFail[theRow] = "Pass"
)
);
);
Here is a script that works based upon the example data you supplied.....and with the above changes. The code uses the limits data table to determine what metals to test.
Names Default To Here( 1 );
dtLimits = Data Table( "Limits sample (1)" );
dtMetals = Data Table( "FG Sample (1)" );
// Get the names of the columns with the metals data in them
metalsColNames = dtLimits << get column names( string, numeric );
For( theRow = 1, theRow <= N Rows( dtMetals ), theRow++,
If( N Rows( dtLimits << get rows where( dtLimits:product == dtMetals:Product[theRow] ) ) > 0,
theProductRow = (dtLimits << get rows where( dtLimits:product == dtMetals:Product[theRow] ))[1];
result = "Pass";
For( theColumn = 1, theColumn <= N Items( metalsColNames ), theColumn++,
target = Column( dtLimits, metalsColNames[theColumn] )[theProductRow];
col = Column( dtMetals, metalsColNames[theColumn] );
If( col[theRow] < target,
result = "Fail"
);
);
dtMetals:Name( "Pass/Fail" )[theRow] = result;
)
);
Jim,
Works great. Thanks again for your help.
Wayne
Hello TxNelson,
I need similar solution and I was trying to follow the one you have provided but not able to get my results. Could you please help me --
I have two tables -- one is data table and other is specs table. I need to compare data in main table with the specs in specs table and put it into a new column as 1 (pass) and 0 (fail).
Any help ?
I have placed below a solution to your request. However, you need to take the time to read the Scripting Guide, to make sure you understand what I have provided, and also to assist you in understanding the JSL provided in other Discussions
names default to here(1);
dtspecs = Data Table ("Specs_Limits");
dt = Data Table ("Table_A");
Loadlimit = dt << Manage Spec Limits( Y( :BVIA_RES, :COILVIA_RES, :RDR_RES, :TAD_RES,
:WELGL_RES), Load from Limits Table("Specs_Limits.jmp"), Save to Column Properties (1) );
Loadlimit << close window;
colNamesList = dt << get column names(string,continuous);
For( theColumn = 1, theColumn <= N Items( colNamesList ), theColumn++,
Clear Symbols( specs );
specs = Column( dt, colNamesList[theColumn] ) << get property( "Spec Limits" );
If( Is Empty( specs ) == 0,
Eval(
Substitute(
Expr(
dt << New Column( colNamesList[theColumn] || " PASS/Fail",
numeric,
CONTINUOUS,
FORMULA( If( __LSL__ <= __col__ <= __USL__, 1, 0 ) )
);
Column( colNamesList[theColumn] || " PASS/Fail" ) << delete formula;
),
Expr( __LSL__ ), specs["LSL"],
Expr( __col__ ), Parse(":" || colNamesLIst[theColumn] ),
Expr( __USL__ ), specs["USL"]
)
)
);
);