Subscribe Bookmark RSS Feed

How do operations involving two separate data table work?

zhouye0

Community Trekker

Joined:

Mar 2, 2016

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
Solution

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

6 REPLIES
Solution

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

Community Trekker

Joined:

Mar 2, 2016

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

Joined:

Jun 23, 2011

(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

Community Trekker

Joined:

Mar 2, 2016

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

anders_bankefor

Community Trekker

Joined:

Dec 8, 2015

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

Community Trekker

Joined:

Dec 8, 2015

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.