topic Re: Add/Subtract values from two jmp files in Discussions

Add/Subtract values from two jmp files

Jackie_ — Fri, 09 Jun 2023 17:01:35 GMT

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

Re: Add/Subtract values from two jmp files

Mark_Bailey — Thu, 16 Jun 2022 17:12:33 GMT

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

Re: Add/Subtract values from two jmp files

Jackie_ — Thu, 16 Jun 2022 19:49:24 GMT

Thanks Mark. The code doesn't seem to work

Re: Add/Subtract values from two jmp files

txnelson — Thu, 16 Jun 2022 19:59:37 GMT

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

Re: Add/Subtract values from two jmp files

Jackie_ — Thu, 16 Jun 2022 20:32:19 GMT

Hey Jim,

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

Re: Add/Subtract values from two jmp files

txnelson — Thu, 16 Jun 2022 22:04:43 GMT

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

Re: Add/Subtract values from two jmp files

Jackie_ — Fri, 17 Jun 2022 12:34:05 GMT

Thanks Jim :)

Re: Add/Subtract values from two jmp files

Jackie_ — Tue, 05 Jul 2022 20:14:51 GMT

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

Re: Add/Subtract values from two jmp files

txnelson — Tue, 05 Jul 2022 20:56:48 GMT

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

The reference data table does not contain any Tests names that match the column names in the Data Table
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]);
```

Re: Add/Subtract values from two jmp files

Jackie_ — Tue, 05 Jul 2022 21:35:33 GMT

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

Re: Add/Subtract values from two jmp files

txnelson — Tue, 05 Jul 2022 22:13:00 GMT

The calculation of "measure" can not be calculated. You have written a line of JSL that assumes that it is looping through a data table, but no looping through the rows is happening.

Re: Add/Subtract values from two jmp files

Jackie_ — Wed, 06 Jul 2022 00:06:26 GMT

Thanks, @txnelson

I used For each rows syntax but the loop takes 40 min to complete. Any alternate way?

For( c = 1, c <= N Col( dt2 ) -1, c++,
	theMatrix = [];
	name = Column( Col_List1[c] ) << Get name;
	if (Contains( Col_List1[c], "Current"),
	//For( r = 1, r <= N Rows( dt2 ), r++,
For Each 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 );
););

Re: Add/Subtract values from two jmp files

Jackie_ — Wed, 06 Jul 2022 15:34:45 GMT

any advice Jim?

Re: Add/Subtract values from two jmp files

txnelson — Wed, 06 Jul 2022 18:26:50 GMT

Your code is confusing to me. When you have

For Each Row

each separate row within the data table is processed.

When the Get Row Where() is used, it is only passing through the data once. Can you provide a sample of the results you are expecting?

Re: Add/Subtract values from two jmp files

Jackie_ — Wed, 06 Jul 2022 19:57:21 GMT

@txnelson
Below is the Screen short of the results that I am expecting. I have attached the final result data table

Here are the steps:

1. Read data table and reference data table

2. Generate a new window to select site number,

3. Based on the user input (Site Number 1 or 2), align those data to the reference data table values

Take mean by wafer Id, apply the offset to the main data table

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",
				get = ct << Get Selected ();
			)
	
	
);


sitenum = Transform Each({str}, get, Num(str));	


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], "Current 2.1") | Contains( Col_List1[c], "Voltage 1.1"),
	For each row(
         r = dt2 << get rows where(dt2:sitenumber == sitenum[1]); /// something needs to change here ****&&&**&&&&**&&&

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

Re: Add/Subtract values from two jmp files

Jackie_ — Thu, 07 Jul 2022 00:16:36 GMT

@txnelson
can you identify what's going wrong