topic Re: Trying to extract spec limits from columns in Discussions

Trying to extract spec limits from columns

matthew5 — Tue, 01 Jul 2014 15:45:29 GMT

Hi,

I am trying to extract column spec limits so I can use in a custom report but I am getting some odd behaviour. Below is a snippet of what I'm doing:

Clear Globals();

dt = Data Table( "blah" );

cols1 = dt << Get Column Names( numeric, continuous );

For( jj = 1, jj <= N Items( cols1 ), jj++,

spec_x = cols1[jj] << Get Property( "Spec limits" );

Print(

Char( jj ) || ": Col is " || Char( cols1[jj] << get name ) ||

", LSL is " || Char( spec_x["lsl"] ) ||

", USL is " || Char( spec_x["usl"] )

);

The print statement is not the real end use, but it serves to show if I've extracted the limits in a useful manner!

Now, this code works fine for a tiny test table of 2 columns:

"1: Col is X, LSL is 1, USL is 2"

"2: Col is Y, LSL is 3, USL is 7"

Which matches what is in my table. But for a larger table of 73 (numeric, continuous) columns, for some reason it fails to put anything into spec_x, I just get:

"1: Col is Rc_N+PO (N/.09/280), LSL is ., USL is ."

"2: Col is Rc_V1 (N/.1/1260), LSL is ., USL is ."

"3: Col is Rc_V2 (N/.1/1188), LSL is ., USL is ."

"4: Col is Rc_V3 (N/.1/1188), LSL is ., USL is ."

"5: Col is Rc_V4 (N/.1/1188), LSL is ., USL is ."

... etc

There is definitely limit data in the table, I have double checked! So is there any reason why it might not be working? The table is open, the script is clearly getting the column names OK, I just can't make it pick up the limits.

When I step though the script and get to spec_x = cols1[jj] << Get Property( "Spec limits" ); the log shows:

Spec Limits( LSL( 10 ), USL( 50 ), Target( 30 ) )

but then when I execute the print statement, it just says:

"1: Col is Rc_N+PO (N/.09/280), LSL is ., USL is ."

Any help appreciated!

Thanks,

Matthew.

Re: Trying to extract spec limits from columns

dkeshock — Tue, 01 Jul 2014 18:04:58 GMT

Does this one work?

Clear Globals();

dt = data table( "blah" );

cols1 = dt << Get Column Names( numeric, continuous, "String" );

Items = N Items(cols1);

For( jj = 1, jj <= Items, jj++,

spec_x = Column(cols1[jj]) << Get Property( "Spec limits" );

Print(

Char( jj ) || ": Col is " || Char( cols1[jj]) ||

", LSL is " || Char( spec_x["lsl"] ) ||

", USL is " || Char( spec_x["usl"] )

);

Re: Trying to extract spec limits from columns

ms — Tue, 01 Jul 2014 19:00:10 GMT

It should work, and it does (JMP 11) in the below example, which is based on your code. Try to insert Wait(0) commands to test if it's a performance problem.

//Make example table

dt = new table("example");

For(i=1, i<=100, i++,

col=dt<<new column("test", numeric, continuous);

eval(evalexpr(col<<set property("Spec Limits",{LSL( expr(i) ), USL( expr(i+2) ), Target( expr(i+1) ), Show Limits( 0 )})))

);

//Print spec limits

cols1 = dt << Get Column Names( numeric, continuous );

For( jj = 1, jj <= N Items( cols1 ), jj++,

spec_x = cols1[jj] << Get Property( "Spec limits" );

Print(

Char( jj ) || ": Col is " || Char( cols1[jj] << get name ) || ", LSL is " || Char( spec_x["lsl"] ) || ", USL is "

|| Char( spec_x["usl"] )

);

Re: Trying to extract spec limits from columns

dkeshock — Tue, 01 Jul 2014 19:12:39 GMT

When I first tested his it worked up #58 of 81 columns I had in an existing table. I found that the Column names switched conventions in the Cols1 variable at the same point (the first 58 were ":ColumnName" and the rest where without the ':') so I switched to using the string names and it worked fine for me.

On your example above, it doesn't run on my end until I change the two "Cols1[jj]" to "Column(cols1[jj])". Is there a reason for that?

Re: Trying to extract spec limits from columns

matthew5 — Wed, 02 Jul 2014 09:30:36 GMT

Thank you both for your replies, but no luck yet. Btw, I am using JMP 11.2 64-bit on Win7.

MS: your code worked fine for me when creating a new test table, but didn't when using my table.

dkeshock: your edit didn't help, but did give me a useful pointer how to easily extract column names, thanks :)

I think I may have a root cause now, but still not sure how to solve it. I copied one of my columns to a new table, then created a new test column with manually entered limits. The script works for the new test column:

"1: Col is Rc_N+PO (N/.09/280), LSL is ., USL is ."

"2: Col is Test, LSL is 1, USL is 5"

So I looked at the scripts for the two columns (deliberately limited to a few rows):

New Column( "Rc_N+PO (N/.09/280)",

Numeric, Continuous, Format( "Best", 10 ),

Set Property( "Spec Limits", Spec Limits( LSL( 10 ), USL( 50 ), Target( 30 ) ) ),

Set Values( [18.306, 18.937, 17.979] )

)

New Column( "Test",

Numeric, Continuous, Format( "Best", 12 ),

Set Property( "Spec Limits", {LSL( 1 ), USL( 5 ), Target( 3 ), Show Limits( 0 )} ),

Set Values( [2, 3, 4] )

)

It looks like the code for the spec limits property is different. The only reason I can think is that for my real data, I imported the spec limits from a data table using the capability platform, whereas for the test column I entered it manually.

Could this be the source of my problem? Is there a way around this, or do I need to enter the spec limits manually (or with a script like the one from MS)?

Thank you,

Matthew.

Re: Trying to extract spec limits from columns

ms — Wed, 02 Jul 2014 11:52:44 GMT

Ok, it appears that, depending on how the spec limits were created, <<get property(...) returns either a list or an expression. I don't know why, but below is en example how to account for both eventualities. I think this approach would work for both lists, expressions and columns without spec limits.

dt=New table("Example");

dt << New Column( "NoSpecLimits", Numeric, Continuous );

dt << New Column( "Rc_N+PO (N/.09/280)",

Numeric,

Continuous,

Set Property( "Spec Limits", Spec Limits( LSL( 10 ), USL( 50 ), Target( 30 ) ) ),

Set Values( [18.306, 18.937, 17.979] )

);

dt << New Column( "Test",

Numeric,

Continuous,

Set Property( "Spec Limits", {LSL( 1 ), USL( 5 ), Target( 3 ), Show Limits( 0 )} ),

Set Values( [2, 3, 4] )

);

cols1 = dt << Get Column Names( numeric, continuous );

For( jj = 1, jj <= N Items( cols1 ), jj++,

Q = cols1[jj] << Get Property( "Spec limits" );

spec_x = Eval List( {Arg( Q, 1 ), Arg( Q, 2 ), Arg( Q, 3 )} );

If( N Row( Loc( spec_x, Empty() ) ),

Print(

Char( jj ) || ": Col " || Char( cols1[jj] << get name ) ||

", has no spec limits"

Print(

Char( jj ) || ": Col is " || Char( cols1[jj] << get name ) || ", LSL is "

|| Char( spec_x["LSL"] ) || ", USL is " || Char( spec_x["USL"] )

)

);

Re: Trying to extract spec limits from columns

matthew5 — Wed, 02 Jul 2014 12:52:44 GMT

That works now, thank you for your help :)

Re: Trying to extract spec limits from columns

MikeD_Anderson — Tue, 12 Dec 2023 17:13:34 GMT

Related to this discussion - here's a neat little function I wrote to handle pulling specs. It uses expression handling and an Associative Array to make a nice little dictionary that can be used later:

getSpecs = Function( {colref},
	{default local}, 
	
	// get the spec limits column property
	plist = colref << Get Property("Spec Limits");
	// some blank lists to hold the values
	keys = {};
	values = {};
	// loop through the property and get the sub-property name and value
	// put those into the correct lists
	For Each( {value, index}, plist,
		Insert Into( keys, Head Name( value ) );
		Insert Into( values, Arg( value, 1) );
	);
	// build an AA and return as the result of the function
	parameters = Associative Array( keys, values );		
);

Use would look something like this:

Names Default To Here( 1 );

Clear Log();

getSpecs = Function( {colref},
	{default local}, 
	
	// get the spec limits column property
	plist = colref << Get Property("Spec Limits");
	// some blank lists to hold the values
	keys = {};
	values = {};
	// loop through the property and get the sub-property name and value
	// put those into the correct lists
	For Each( {value, index}, plist,
		Insert Into( keys, Head Name( value ) );
		Insert Into( values, Arg( value, 1) );
	);
	// build an AA and return as the result of the function
	parameters = Associative Array( keys, values );		
);
 
// open a data table with specs
dt = Open("$Sample_Data/Quality Control/Vial Fill Weights.jmp");

// get the specs from the column
specs = getSpecs(Column("Fill Weight"));

// do something with the values
lsl = specs["LSL"];
tgt = specs["Target"];
usl = specs["USL"];

show(lsl, tgt, usl);