topic Re: What is the best way to calculate with table variables and columns? in Discussions

What is the best way to calculate with table variables and columns?

Thomas1 — Mon, 06 Aug 2018 22:32:40 GMT

A JSL (TestJSL) script creates two table variables. Within the script they are being used to calculate a new column :D1 from the existing column :D0 by applying IF states etc..

Is there a better way to do this, than with the rather complex formula for :D1?

Re: What is the best way to calculate with table variables and columns?

gzmorgan0 — Tue, 07 Aug 2018 06:47:07 GMT

There are multiple ways to do this without having a table formula and table variables using set values.

Since your script used a formula, this alternative JSL uses a formula. If you can get by without a column formula, other methods can be used, including a script to prompt for quantile, threshold then recalculate qq and recalculate D1.

//Create Table Variable
dt = currentdatatable();
dt << Set Table Variable( "Quantile", 0.9 );
dt << Set Table Variable( "Threshold", 1 );

_xx = dt << get rows where(:P1 ==0 & :P2 <:Threshold);
dt << Set Table Variable("QQ", Quantile(dt:Quantile, dt:D0[_xx] ));
dt << New Column("D1",  Numeric, Continuous, 
  Formula( If(:P1 ==0 & :P2 <:Threshold & :D0< :QQ, :D0) )
);

Re: What is the best way to calculate with table variables and columns?

Thomas1 — Tue, 07 Aug 2018 09:34:58 GMT

Thanks for your solution. It works fine. However, after the JSL was executed and I do readjust the table variable "Quantile" and "Threshold" the new column :D1 is only responding on changes on "Threshold" and not on changes on "Quantile". Could this be fixed?

@gzmorgan0 wrote:
There are multiple ways to do this without having a table formula and table variables using set values.

Since your script used a formula, this alternative JSL uses a formula. If you can get by without a column formula, other methods can be used, including a script to prompt for quantile, threshold then recalculate qq and recalculate D1.
//Create Table Variable
dt = currentdatatable();
dt << Set Table Variable( "Quantile", 0.9 );
dt << Set Table Variable( "Threshold", 1 );

_xx = dt << get rows where(:P1 ==0 & :P2 <:Threshold);
dt << Set Table Variable("QQ", Quantile(dt:Quantile, dt:D0[_xx] ));
dt << New Column("D1",  Numeric, Continuous, 
  Formula( If(:P1 ==0 & :P2 <:Threshold & :D0< :QQ, :D0) )
);
Thanks for your solution. It works fine. However, after the JSL was executed and I do readjust the table variable "Quantile" and "Threshold" the new column :D1 is only responding on changes on "Threshold" and not on changes on "Quantile".

Re: What is the best way to calculate with table variables and columns?

gzmorgan0 — Tue, 07 Aug 2018 10:14:07 GMT

Well your formula worked, so you could use that. I agree that formula is a difficult one to maintain.

Here is an alternative, add a script to the table. I added the JSL (after the wait(5) ) that shows how to change the quantile and threshold and run the script. Or interactively, change the 2 variables and click on Update D1.

Use whichever method you find easiest for you to use.

//Create Table Variable
dt = currentdatatable();
dt << Set Table Variable( "Quantile", 0.9 );
dt << Set Table Variable( "Threshold", 1 );
dt << New Column("D1",  Numeric, Continuous);
dt << New Script ("Update D1",
   dt = current data table();
  _xx = dt << get rows where(:P1 ==0 & :P2 <:Threshold);
  dt << Set Table Variable("QQ", Quantile(dt:Quantile, dt:D0[_xx] ));
   qq = Quantile(dt:Quantile, dt:D0[_xx] );
   //caption(char(qq)); wait(2); caption(remove); //uncomment to test
  dt:D1 << Set Each Value(
    If(:P1 ==0 & :P2 <:Threshold & :D0< qq, :D0, empty()) 
 );
);
wait(5);
dt << Set Table Variable( "Quantile", 0.6 );
dt << Set Table Variable( "Threshold", 1.8 );
dt << Run Script("Update D1") ;

Re: What is the best way to calculate with table variables and columns?

Thomas1 — Tue, 07 Aug 2018 12:47:51 GMT

Thanks again. Now it works, by running the update script. The charm of the formula is that the data update is automatically done. Is it risky to do this with the formula for about 100k rows (avoiding errors ….)? If I stick with the formula would it be better to generate an column :DT in which to first part is being calculated If( :P1 == 0 & :P2 < :Threshold, :D0 ) What would a script look like in case there are several: D0a :D0b columns in order to generate the corresponding : D1a, .D1b columns?