Trying to extract spec limits from columns

[#1]

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.

[#2]

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

);

);

[#3]

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

  );

);

[#4]

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?

[#5]

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.

[#6 · Accepted solution]

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

  )

  );

);

[#7]

That works now, thank you for your help :-)

[#8]

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