Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- Re: Need to compare result from one table with spec limit in another table. If ...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Feb 1, 2019 1:01 PM
(3565 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

- The various metals columns in the limits file need to be numeric columns.
- The Pass/Fail column in the Metals data table needs to be defined as character.

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

21 REPLIES 21

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Need to compare result from one table with spec limit in another table. If result < limit th

I have attached sample files.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Need to compare result from one table with spec limit in another table. If result < limit th

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

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Need to compare result from one table with spec limit in another table. If result < limit th

JIM,

In my limit file I have multiple products. Fudd with Aluminum and the limit .05 then Miracle with Aluminum and the limit .02. Each product in the limits file is under the product column. Example Product, Aluminum, Zinc

Fudd .5 .1

Miracle .2 .5

Please let me know how to handle the product.

Regards,

Wayne

In my limit file I have multiple products. Fudd with Aluminum and the limit .05 then Miracle with Aluminum and the limit .02. Each product in the limits file is under the product column. Example Product, Aluminum, Zinc

Fudd .5 .1

Miracle .2 .5

Please let me know how to handle the product.

Regards,

Wayne

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Need to compare result from one table with spec limit in another table. If result < limit th

All that has to happen is to find the correct row in the Limits Table, for the Product on the row in the Metals data table. I will get you a modification of my script that will do that......but don't you need to have more than one Pass/Fail column in your metals data table, because each metal can either pass or fail.....or are you saying that if any metal fails, then it is a fail, but if they all pass then the row passes????

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Need to compare result from one table with spec limit in another table. If result < limit th

If any metal fails, then the sample fails. If all metals pass, then the sample passes.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

- The various metals columns in the limits file need to be numeric columns.
- The Pass/Fail column in the Metals data table needs to be defined as character.

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Need to compare result from one table with spec limit in another table. If result < limit th

Jim,

Works great. Thanks again for your help.

Wayne

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Need to compare result from one table with spec limit in another table. If result < limit th

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 ?

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Need to compare result from one table with spec limit in another table. If result < limit th

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"]
)
)
);
);
```

Jim

Article Labels

There are no labels assigned to this post.