We’re asking you to select a content label when starting a new topic in the Discussions area. Read more to find out why.
Choose Language Hide Translation Bar
Highlighted
kwmcm
Level II

Linking a data table for reference limits

Hi all, 

I'm currently trying to link (not sure if this is the best term) two data tables. 

dt1 is raw data (multiple levels of a control with concentration values) *see example below*

image.png

dt2 is spec limits (multiple levels of spec limits, Alert and Customer) *see example below*

image.png

 

Ideally, I'd like to open dt1, then open dt2, and have those values set as Table Variables. 

image.png 

This is an example of the Low Control. 

I'd update the titles to Low_C_LSL (Low Customer LSL), Low_A_LSL (Low Alert LSL), Med_C_LSL, etc. so that it would be clear. since there would be 15 table variables. 

 

Once the Table Variables are set, I'll be using them for the Inverse Prediction model.

 

I've thought about splitting out the Low, Medium, and High, but since we have 13 products with three levels, and do most of our initial spreadsheet set up in Excel, going from one sheet per product to three would be tedious and confusing.  

 

I've looked at so many other discussions and the scripting index, but not finding exactly what I need. 

 

I currently have this jsl written, but instead of having 13 scripts for 13 products, I'd like to have one script and import the specs. 

It basically just opens the Limits Table, but does nothing with it. 

 

Names Default To Here( 1 );

path = Pick File( "Select Data Table" );
dt = Open( path );

path = Pick File( "Select Limits Table" );
dt2 = Open( path );

Current Data Table( dt );

dt << Set Table Variable( "Low Customer LSL", 15 );
dt << Set Table Variable( "Low Alert LSL", 20 );
dt << Set Table Variable( "Low Target", 40 );
dt << Set Table Variable( "Low Alert USL", 60 );
dt << Set Table Variable( "Low Customer USL", 65 );

 

 

Any direction would be appreciated! 

Thanks so much!

 

2 REPLIES 2
Highlighted
txnelson
Super User

Re: Linking a data table for reference limits

All you need to do, is to loop through the dt2 data table, setting each value found into the dt data table variables, using the name construction you have specified.  See below:

Names Default To Here( 1 );

path = Pick File( "Select Data Table" );
dt = Open( path );

path = Pick File( "Select Limits Table" );
dt2 = Open( path );

For( i = 2, i <= N Cols( dt2 ), i++,
	For( k = 1, k <= N Rows( dt2 ), k++,
		dt << New Table Variable(
			dt2:SampleID[k] || "_" || 
			Char( Column( dt2, i ) << get name ),
			Column( dt2, i )[k]
		)
	)
);

 

Jim
Highlighted
kwmcm
Level II

Re: Linking a data table for reference limits

That works perfectly!

Part 2 of my question would be how can I specify that LOW_A_LSL, etc etc is ONLY for the Low Control?

I tried a **where :SampleID == "LOW" ** piece, and only added the LOW LSLs/ASLs, etc but it still adds them to the Medium and High.

Any thoughts on that?

 

fit << Inverse Prediction(
	Response(
		:LOW_A_LSL,
		:MED_A_LSL,
		:HIGH_A_LSL,
		:LOW_A_USL,
		:MED_A_USL,
		:HIGH_A_USL,
		:LOW_C_LSL,
		:MED_C_LSL,
		:HIGH_C_LSL,
		:LOW_C_USL,
		:MED_C_USL,
		:HIGH_C_USL
	),
	Term Value( Elapsed( . ) )
) << Inverse Prediction(
	Response(
		:LOW_A_LSL,
		:MED_A_LSL,
		:HIGH_A_LSL,
		:LOW_A_USL,
		:MED_A_USL,
		:HIGH_A_USL,
		:LOW_C_LSL,
		:MED_C_LSL,
		:HIGH_C_LSL,
		:LOW_C_USL,
		:MED_C_USL,
		:HIGH_C_USL
	),
	Term Value( Elapsed( . ) ),
	Individual
);