topic Re: Add values from another data table file to current data table file in Discussions

Add values from another data table file to current data table file

Jackie_ — Fri, 09 Jun 2023 19:55:28 GMT

Hi,

Hi, I have a doubt regarding JSL script (very new to this!)

I've two data files- Table A and Table B.

Table A:-

Table B:-

I want to add values from Table B Column name "Offset" to each Rows of Column Group "Tests" in Table A and generate a new data table file with values added.

P.S.- There are more than 100 columns in the Table A file. How can I use a for loop to iterate all rows and columns?

Here's what I've tried to start with. Any ideas on this or a more efficient way to do it?

Names Default To Here( 1 );
dt = Data Table( "Table A" );
dtOffset = Data Table( "Table B" );

For Each Row(
	dt:Current = dt:Current + dtOffset:Offset[1];
	dt:Voltage = dt:Voltage + dtOffset:Offset[2];
	dt:Resistance = dt:Resistance + dtOffset:Offset[3];
	dt:Inductance = dt:Inductance + dtOffset:Offset[4];
	dt:Capacitance = dt:Capacitance + dtOffset:Offset[5];
	dt:Power = dt:Power + dtOffset:Offset[6];
	dt:Temperature = dt:Temperature + dtOffset:Offset[7];
	dt:Humidity = dt:Humidity + dtOffset:Offset[8];
	dt:Pressure = dt:Pressure + dtOffset:Offset[9];
	dt:Suseptance = dt:Suseptance + dtOffset:Offset[10];
);

@txnelson @ih @jthi @Jeff_Perkinson

Re: Add values from another data table file to current data table file

txnelson — Thu, 26 Aug 2021 01:48:25 GMT

Please take the time to read the Scripting Guide and to familiarize yourself with the Scripting Index.
If you need to brush up on general programming skills, there are a large number of sites on the internet that can assist with that.

Here is the simple script that will do what you want

Names Default To Here( 1 );
dt = Data Table( "Table A" );
dtOffset = Data Table( "Table B" );

For Each Row(
	dt:Current = dt:Current + dtOffset:Offset[1];
	dt:Voltage = dt:Voltage + dtOffset:Offset[2];
	dt:Resistance = dt:Resistance + dtOffset:Offset[3];
	dt:Inductance = dt:Inductance + dtOffset:Offset[4];
	dt:Capacitance = dt:Capacitance + dtOffset:Offset[5];
	dt:Power = dt:Power + dtOffset:Offset[6];
	dt:Temperature = dt:Temperature + dtOffset:Offset[7];
	dt:Humidity = dt:Humidity + dtOffset:Offset[8];
	dt:Pressure = dt:Pressure + dtOffset:Offset[9];
	dt:Suseptance = dt:Suseptance + dtOffset:Offset[10];
);

Re: Add values from another data table file to current data table file

Jackie_ — Thu, 26 Aug 2021 12:54:28 GMT

Many Thanks @txnelson you are genius :)

Re: Add values from another data table file to current data table file

Jackie_ — Thu, 26 Aug 2021 12:56:22 GMT

How can I use a loop to perform addition. There are more than 100 columns in Table A?

Re: Add values from another data table file to current data table file

txnelson — Thu, 26 Aug 2021 13:44:55 GMT

You can use

colList = dt << get column names;

to get the names of the columns in the data table

and then use a For() loop

For(i=1,i<=N Items(colList), i++,
.......
.......
.......
);

to perform the operations you need to do for each column.

These are documented in the Scripting guide and Scripting Index......with examples

Re: Add values from another data table file to current data table file

Jackie_ — Thu, 26 Aug 2021 18:20:37 GMT

@txnelson

Here is what I tried. Im not sure what's wrong. Can you please take a look?

Names Default To Here( 1 );
dt = Data Table( "Table A" );
dtOffset = Data Table( "Table B" );

colList = dt << get column names;

For(i=1; N Rows(dt); i++,

	Column(dtOffset)[i] = dt:colList[i] + dtOffset:Offset[i];
	
);

Re: Add values from another data table file to current data table file

Jackie_ — Thu, 26 Aug 2021 20:23:42 GMT

Hi @ih,

I want to add values from Table B Column name "Offset" to each Rows of Column Group "Tests" in Table A and generate a new data table file with values added. There are more than 100 columns in Table A. How can I use a loop syntax to iterate to each rows and columns?

Here's what I've tried to start with

Names Default To Here( 1 );
dt = Data Table( "Table A" );
dtOffset = Data Table( "Table B" );

colList = dt << get column names;

For(i=1; N Rows(dt); i++,

	Column(dtOffset)[i] = dt:colList[i] + dtOffset:Offset[i];
	
);

Appreciate if you could suggested

Re: Add values from another data table file to current data table file

txnelson — Thu, 26 Aug 2021 20:30:47 GMT

The log file entry from when your code is run is:

not enough arguments in access or evaluation of 'For' , For/*###*/(i = 1 ; N Rows( dt ) ; i++, Column( dtOffset )[i] = dt:colList[i]
+dtOffset:Offset[i])

at line 7 in Script 5.jsl

What this is telling you, is that the For() function syntax that you are using is incorrect.

The correct definition can be found in the Scripting Index, or in the Scripting Guide. Also, if you look at your implementation of the For() function, and the sample I provided, your version is not the same.

Your solution is going to require that you loop across the columns in your Table A. For each column you will need to get the Offset value that needs to be applied to the values in the Table A column, and then loop through all of the rows in that column, applying the correct offset.

Names Default To Here( 1 );
dt = Data Table( "Table A" );
dtOffset = Data Table( "Table B" );

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

For( i = 1, i <= N Items( colList ), i++, 
	// Find the offset value in the dtOffset(Table B) data table
	// Because the column name from Table A may not have an offset
	// in Table B that has to be accounted for
	theRow = dtOffset << get rows where( dtOffset:Tests == colList[i] );
	show(i,nrows(therow));
	// If no rows are returned, no offset was found
	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 values from another data table file to current data table file

txnelson — Thu, 26 Aug 2021 20:36:12 GMT

The Community Discussion Forum is not a place to go to to have JMP scripts written for you! You need to take the time to learn JSL, and to use the Discussion Forum to help you with specific issues you are not understanding.

Re: Add values from another data table file to current data table file

Jackie_ — Fri, 27 Aug 2021 14:58:51 GMT

Well, its not just me. I have seen similar posts before