Add/Subtract values from two jmp files

[#1]

Hi,

@txnelson @pmroz 

I need help to offset the data set using the reference value in JSL 

First table contains reference value

 
Second table contains strips tested measurements

I want to subtract mean of strip A from the reference data table and then add to to the data set 
for example :

step1. the mean value for strips A for current = (10+3)/2 = 6.5. So, 400 - 6.5 = 393.5 and likewise for other strips

step2. Add 393.5 to the 2nd data table of strip A >> 393.2 + 10 = 403.2 and likewise for other strips

The final table should have following values:

Refer attached file for details.

Thank you for your help!

This what I tried:

Names Default To Here( 1 );
dt = Current data table();
dtOffset = Open(Limits);

colList = dt << get column names(string);

For( i = 1, i <= N Items( colList ), i++, 
	
	theRow = dtOffset << get rows where( dtOffset:Tests == colList[i] );
	show(i,nrows(therow));
	
	If( N Rows( theRow ) == 1,
		offset = dtOffset:Offset[theRow[1]];
		For( k = 1, k <= N Rows( dt ), k++,
			Column( dt, i )[k] = Column( dt, i )[k] + offset
		);
	);
	);

[#2]

Please try this example:

Names Default to Here( 1 );

dt1 = Open( "Reference data.jmp" );
dt2 = Open( "Data set.jmp" );

For( c = 1, c <= N Col( dt2 ), c++,
	name = Column( dt2, c ) << Get Name;
	mean = dt1:Mean[dt1 << Get Rows Where( :Tests == name )];
	measure = mean - (Column dt2, c ) << Get As Matrix);
	Column( dt2, c) << Set Values( measure );
);

[#3]

Thanks Mark. The code doesn't seem to work 

[#4]

Thee was a misplaced "(".  Here is a corrected version

Names Default To Here( 1 );

dt1 = Open( "Reference data.jmp" );
dt2 = Open( "Data set.jmp" );

For( c = 1, c <= N Col( dt2 ), c++,
	name = Column( dt2, c ) << Get Name;
	mean = dt1:Mean[dt1 << Get Rows Where( :Tests == name )];
	measure = mean - Column( dt2, c ) << Get As Matrix;
	Column( dt2, c ) << Set Values( measure );
);

[#5]

Hey Jim,

Thanks for correcting but it didn't seem to work either.

[#6 · Accepted solution]

I was making changes to Mark's code, based upon syntax issues, not logic issues.  I have reworked the code and I believe this will work

Names Default To Here( 1 );

dt1 = open( "Reference data.jmp" );
dt2 = open( "Data set.jmp" );

For( c = 1, c <= N Col( dt2 ) -1, c++,
	theMatrix = [];
	name = Column( dt2, c ) << Get Name;
	For( r = 1, r <= N Rows( dt2 ), r++,
		mean = dt1:Mean[dt1 << Get Rows Where( :Tests == name )];
		measure = mean - Col Mean( If( dt2:Strips == dt2:Strips[r], Column( dt2, c ), . ) );
		theMatrix = theMatrix || Column( dt2, c )[r] + measure;
	);
	Column( dt2, c ) << Set Values( theMatrix );
);

[#7]

Thanks Jim

[#8]

Hi Jim @txnelson ,

Quick question:

I modified the jsl script and want to filter rows based on the check box selection. 

The below script will create a new window, ask user to select the check box for site number.

I want to only add mean offset to the SiteNumber selection.

If SiteNumber 1 is selected, align only those rows . I have attached both files

I tried using get rows where but the results doesn't seem to be accurate. Looking for some suggestions

Names Default To Here( 1 );
Clear globals();
Clear log();
dt1 = Data table( "Reference data table" );
dt2 = Current data table(); /// Select Data table.jmp




For(i=1,i<=nrows(dt1),i++,
	theTest = dt1:Tests[i];
	try(column(dt2,theTest) << set property("TheMean", dt1:Mean[i]));
);

New window( "", modal,
			ct = checkbox({"1","2"}),
			Buttonbox ("Ok",
				ft = ct << Get Selected Indices();
			), ct << set all(1);
	
	
);

sitenum = {2};


//Insert Into (sitenum, ft);


Col_List1 = dt2 << Get Column Names( "String" );

//if (!contains(Col_List1, "SiteNumber"),
     // dt2 <<New Column("SiteNumber", "Numeric", "Continuous", <<Set Each Value( 1 ) );
     
   //   );


For( c = 1, c <= N Col( dt2 ) -1, c++,
	theMatrix = [];
	name = Column( Col_List1[c] ) << Get name;
	if (Contains( Col_List1[c], "Current 1.1") | Contains( Col_List1[c], "Voltage 1.7"),
	//For( r = 1, r <= N Rows( dt2 ), r++,
	//For Each Row( r=row();
	
        r = dt2 << get rows where(contains(sitenum, dt2:SiteNumber)); ///** get rows based upon the check box input
		//mean = dt1:Mean[dt1 << Get Rows Where( :Tests == name )];
		mean = column(dt2, Col_list1[c]) << get property("theMean");
		//measure = mean - Col Mean( If( dt2:Strips == dt2:Strips[r], Column( dt2, c ), . ) );
		measure = mean - Col Mean(as Column( dt2, c ),:Wafer ID);
		theMatrix = theMatrix || Column( dt2, c )[r] + measure;
	
	Column( dt2, c ) << Set Values( theMatrix );
););

[#9]

Doing a quick look at the script, a couple of items pop out.

1. The reference data table does not contain any Tests names that match the column names in the Data Table 
2. The Get Rows Where() will not work.  Are you attempting to find all rows that match the sitenum?  If so it shoul be

   r = dt2 << get rows where(dt2:sitenumber == sitenum[1]);

[#10]

@txnelson 

Apologize! I attached wrong files. Below are the updated data tables.

I tried but the results aren't accurate 

Names Default To Here( 1 );
Clear globals();
Clear log();
dt1 = Data table( "Reference data table" );
dt2 = Current data table(); /// Select Data table.jmp




For(i=1,i<=nrows(dt1),i++,
	theTest = dt1:Tests[i];
	try(column(dt2,theTest) << set property("TheMean", dt1:Mean[i]));
);

New window( "", modal,
			ct = checkbox({"1","2"}),
			Buttonbox ("Ok",
				ft = ct << Get Selected Indices();
			), ct << set all(1);
	
	
);

sitenum = {};


Insert Into (sitenum, ft);


Col_List1 = dt2 << Get Column Names( "String" );

//if (!contains(Col_List1, "SiteNumber"),
     // dt2 <<New Column("SiteNumber", "Numeric", "Continuous", <<Set Each Value( 1 ) );
     
   //   );



For( c = 1, c <= N Col( dt2 ) -1, c++,
	theMatrix = [];
	name = Column( Col_List1[c] ) << Get name;
	if (Contains( Col_List1[c], "Current 1.1") | Contains( Col_List1[c], "Voltage 1.7"),
	//For( r = 1, r <= N Rows( dt2 ), r++,
	//For Each Row( r=row();
	r = dt2 << get rows where(dt2:SiteNumber == sitenum[1]);
		//mean = dt1:Mean[dt1 << Get Rows Where( :Tests == name )];
		mean = column(dt2, Col_list1[c]) << get property("theMean");
		//measure = mean - Col Mean( If( dt2:Strips == dt2:Strips[r], Column( dt2, c ), . ) );
		measure = mean - Col Mean(as Column( dt2, c ),:Wafer ID);
		theMatrix = theMatrix || Column( dt2, c )[r] + measure;
	
	Column( dt2, c ) << Set Values( theMatrix );
););