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

How do operations involving two separate data table work?

zhouye0 — Mon, 09 May 2016 15:35:43 GMT

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?

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

ian_jmp — Mon, 09 May 2016 16:07:14 GMT

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

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

zhouye0 — Tue, 10 May 2016 10:15:29 GMT

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?

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

ian_jmp — Tue, 10 May 2016 10:30:57 GMT

(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'.

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

zhouye0 — Tue, 10 May 2016 11:24:45 GMT

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

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

anders_bankefor — Tue, 31 May 2016 07:58:23 GMT

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

);

Anders

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

anders_bankefor — Tue, 31 May 2016 08:18:26 GMT

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.