cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
zhouye0
Level II

How do operations involving two separate data table work?

For example, when I want to divide each value of column 1 from data table 1 by each value of column 2 from data table 2​. I seem to have issues doing so and I am wondering why this error pops up "Scoped data table access requires a data table column or variable{1} in access or evaluation of 'dtbinsum:column' , dtbinsum:column/*###*/(Nrows) /*###*/"

The part of my script looks like this:

//Start of script

dt = currentdatatable();

dtbinsum = dt << Summary(

  Group( :Data File, :Lot id, :SBIN ),

  Freq( "None" ),

  Weight( "None" ),

  statistics column name format( "column" ),

  Link to original data table( 0 )

  );

dtbinsum<< Select Where( dtbinsum:sbin != 1 );

  dtbinsum<< Delete Rows;

dtbinsum2 = dt << Summary(

  Group( :Lot id ),

  N(:SBIN),

  Freq( "None" ),

  Weight( "None" )

  );

yield = {};

for(i=0, i<Nrows(dtbinsum2), i++,

  yield_temp = (dtbinsum:column(Nrows))/(dtbinsum2:column(N(SBIN)));

  Insert Into(yield,yield_temp);

);

Show(yield);

//End of script

I know my way is probably not the most efficient way to do it, not to mention the error that shows, but does anyone have a solution to this?

1 ACCEPTED SOLUTION

Accepted Solutions
ian_jmp
Staff

Re: How do operations involving two separate data table work?

Something like this maybe:

NamesDefaultToHere(1);

// Make a couple of tables

v1 = J(10, 1, RandomInteger(1, 5));

v2 = J(10, 1, RandomInteger(1, 5));

dt1 = NewTable("Table 1", NewColumn("Numerator", Numeric, Ordinal, Values(v1)));

dt2 = NewTable("Table 2", NewColumn("Denominator", Numeric, Ordinal, Values(v2)));

// Add a quotient column to Table 1

Wait(3);

vals = (Column(dt1, "Numerator") << getValues) :\ (Column(dt2, "Denominator") << getValues);

dt1 << NewColumn("Quotient", Numeric, Continuous, Values(vals));

View solution in original post

6 REPLIES 6
ian_jmp
Staff

Re: How do operations involving two separate data table work?

Something like this maybe:

NamesDefaultToHere(1);

// Make a couple of tables

v1 = J(10, 1, RandomInteger(1, 5));

v2 = J(10, 1, RandomInteger(1, 5));

dt1 = NewTable("Table 1", NewColumn("Numerator", Numeric, Ordinal, Values(v1)));

dt2 = NewTable("Table 2", NewColumn("Denominator", Numeric, Ordinal, Values(v2)));

// Add a quotient column to Table 1

Wait(3);

vals = (Column(dt1, "Numerator") << getValues) :\ (Column(dt2, "Denominator") << getValues);

dt1 << NewColumn("Quotient", Numeric, Continuous, Values(vals));

zhouye0
Level II

Re: How do operations involving two separate data table work?

Thanks Ian, it works for me but can I ask you about this line in particular?

vals = (Column(dt1, "Numerator") << getValues) :\ (Column(dt2, "Denominator") << getValues);

                                                                       ^

The way I understand JSL is that colons that helps identify columns,  if used like this, how does it work?

ian_jmp
Staff

Re: How do operations involving two separate data table work?

(Column(dt1, "Numerator") << getValues) puts the values from the specified column into a row vector (which, in the example, is the same as v1). (Column(dt2, "Denominator") << getValues) does the same thing with the other column (which is actually the same row vector as v2). Then the :\ operator asks for an element-wise division, that is, divide the first element of the first vector by the first element of the second vector to get the first element of the result vector (and so on for the other elements).

Take a look at the 'Matrices' section in Chapter 7 of  'Help > Books > Scripting Guide'.

zhouye0
Level II

Re: How do operations involving two separate data table work?

Thanks Ian, now that you pointed out that its element wise operations, it makes a lot of sense now.

anders_bankefor
Level III

Re: How do operations involving two separate data table work?

Could you give me suggestions of how I modify your formula to work in a for-loop?

it should do the same thing as your script but for all columns, the name for the new table should be the header from column aquiered + Quotient

dt1 = data table ("main table");

dt2 = data table ("ref table");

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

for ( i = 2, i <= N cols (myTable), i++,

// i've tried different versions of a reference to the columns in my main table but I cannot get it to work

vals = (Column(dt1, column(i) ) << getValues) :\ (Column(dt2,  column(i) ) << getValues);

dt1 << NewColumn( cols , Numeric, Continuous, Values(vals));

);

BR

Anders

anders_bankefor
Level III

Re: How do operations involving two separate data table work?

this is how far I got before I recevied the following error:

Name Unresolved: in acess or evaluation of " , Name(" ")/*###*/

code:

dt1 = data table ("main table");

dt2 = data table ("REF table split");

cols = dt1 << get column names( string );

for ( i = 2, i <= N cols (dt1), i++,

one_column = cols[i];

vals = (Column(dt1, one_column) << getValues) :\ (Column(dt2, one_column) << getValues);

dt1 << NewColumn( one_column, Numeric, Values(vals));

 

);

the script manages to make one turn in the loop thus creating one column with correct values.