8859_en-US

liliconcom

Trying to extract spec limits from columns<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

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

That works now, thank you for your help

Re: Trying to extract spec limits from columns

Ok, it appears that,  depending on how the spec limits were created, <

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

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

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

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

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

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