topic Re: Exporting Variance Components Table of Variability Chart in Discussions

Exporting Variance Components Table of Variability Chart

sbruno — Tue, 27 Mar 2018 11:09:44 GMT

Hello,

i don't know how to populate a new table with the data output of a Variability Chart with jsl code. I load a table input excel file, i run a bivariate and then a variability chart..but i'm not able to get the values of the tables reported in the output and fill a new table with that data..Below i report the code that perform the analysis.

thak you for any suggestion,

Simone

dt = Open("myfile.xlsx",Worksheets("Sheet1"));

dt << New Column( "LN theoretical Relative Potency %",
Numeric,
"Continuous",
Format( "Fixed Dec", 12, 3 ),
Formula( LnZ( :Theoretical Relative Potency % ) )
);
dt << New Column( "LN Result (RP%)",
Numeric,
"Continuous",
Format( "Fixed Dec", 12, 3 ),
Formula( LnZ( :Name( "Result (RP%)" ) ) ),
Set Selected
);

Bivariate(
Y( :Name( "LN Result (RP%)" ) ),
X( :LN theoretical Relative Potency % ),
Fit Line( {Line Color( {213, 72, 87} )} )
);

dt1 = Variability Chart(
Y( :Name( "LN Result (RP%)" ) ),
X( :Trial ),
Model( "Nested" ),
Max Iter( 100 ),
Conv Limit( 0.00000001 ),
Number Integration Abscissas( 128 ),
Number Function Evals( 65536 ),
Analysis Type( "Choose best analysis (EMS REML Bayesian)" ),
Std Dev Chart( 1 ),
Variance Components( 1 ),
By( :LN theoretical Relative Potency % )
);

Re: Exporting Variance Components Table of Variability Chart

txnelson — Tue, 27 Mar 2018 14:26:27 GMT

Below is a modified version of your script that will output the tables I think you want output? I have included in the script, comments on what the changes are, and what their purposes are. The documentation on how to access the report tree structures of the output is found in

Help==>Books==>Scripting Guide

It is necessary reading if you want to get into JSL

dt = Open( "myfile.xlsx", Worksheets( "Sheet1" ) );

dt << New Column( "LN theoretical Relative Potency %",
	Numeric,
	"Continuous",
	Format( "Fixed Dec", 12, 3 ),
	Formula( LnZ( :Theoretical Relative Potency % ) )
);
dt << New Column( "LN Result (RP%)",
	Numeric,
	"Continuous",
	Format( "Fixed Dec", 12, 3 ),
	Formula( LnZ( :Name( "Result (RP%)" ) ) ),
	Set Selected
);

Bivariate(
	Y( :Name( "LN Result (RP%)" ) ),
	X( :LN theoretical Relative Potency % ),
	Fit Line( {Line Color( {213, 72, 87} )} )
);

// Create a Handle/Pointer to the output display so it can be
// referenced
var = dt << Variability Chart(
	Y( :Name( "LN Result (RP%)" ) ),
	X( :Trial ),
	Model( "Nested" ),
	Max Iter( 100 ),
	Conv Limit( 0.00000001 ),
	Number Integration Abscissas( 128 ),
	Number Function Evals( 65536 ),
	Analysis Type( "Choose best analysis (EMS REML Bayesian)" ),
	Std Dev Chart( 1 ),
	Variance Components( 1 ),
	By( :LN theoretical Relative Potency % )
);

// Point to the display report tables you want to generate the new data tables from
// and sent the Make into Data Table message to each of them.  Note the dtANOVA and
// dtVarComp, will become the pointers to the newly created data tables
dtANOVA = report(var)["Analysis of Variance"][TableBox(1)] << Make into Data Table;
dtVarComp = report(var)["Variance Components"][TableBox(1)] << Make into Data Table;

Re: Exporting Variance Components Table of Variability Chart

sbruno — Thu, 29 Mar 2018 10:52:09 GMT

Dear Jim,

thank you for your suggestion. I'm trying to find how to write the data of the variable "dtVarComp" into excel file in the "Scripting Guide"...but i really don't understand. I think that i have to create a new table before and then export it, but how can i populate the rows and the columns? how can i access the elements of the "dtVarComp" variable?

This is frustrating but i will find a solution!!

thank you

Simone

Re: Exporting Variance Components Table of Variability Chart

txnelson — Thu, 29 Mar 2018 13:05:20 GMT

Are you asking how do you save the data table created from the Variability Components Analysis back into Excel?

If so, then to save the data table created can be saved as

dtVarComp << Save As("Path to folder\Name.xls");

Re: Exporting Variance Components Table of Variability Chart

sbruno — Mon, 09 Apr 2018 09:31:51 GMT

Dear Jim,

I tried the commands you wrote but i have always an error when using the command "Make into Data Table". In the Log file i found:

"Send Expects Scriptable Object in access or evaluation of 'Send' , Report( var )["Analysis of Variance"][Table Box( 1 )] << /*###*/
Make into Data Table/*###*/"

Actually the report of Variance Components creates 5 tables. the name of the table in which i'm interested in are: TableBox(2), TableBox(4), TableBox(6), TableBox(8), TableBox(10).

I tryed to change "Table Box( 1 )" in one of the table names listed above but i'm always the same error..I googoled a lot but i didn't find a solution..and the log informations are really poor..

I attach in a word file the script and the log..If you could give me some other suggestions on how to fix this error..

thank you very much

Re: Exporting Variance Components Table of Variability Chart

txnelson — Mon, 09 Apr 2018 12:16:18 GMT

Your issue is that you are using a "By" element in your Variability Chart. When this is done, a separate Tree Structure is created for each of the "By" groups. Therefore, you need to reference which of the "By" groups you want to reference. I am assuming that you want to output all of the Analysis of Variance tables for all of your "By" groups, and likewise, all of the Variance Components. So you will want to not want to "Make into Data Table", but rather, "Make Combined Data Table". Here is the code modification I believe you will want:

dtANOVA = report(var[1])["Analysis of Variance"][TableBox(1)] << Make Combined Data Table;
dtVarComp = report(var[1])["Variance Components"][TableBox(1)] << Make Combined Data Table;

Re: Exporting Variance Components Table of Variability Chart

sbruno — Mon, 09 Apr 2018 13:58:54 GMT

Thank you very much Jim...this is exactly what i need..:-)
thank you
thank you
...
thank you

Simone