Looping through variables inside a formula

[#1]

Hi,

For a project I´m working on I have created a data table column (Animal No.Avg) where I am calculating some relative values for VO2 based on a baseline value (Treatment C) from table GroupAvg.

In this case there is only one baseline value per assessment (VO2, RQ, EE) as seen in groupAvg as it is an average of several days. For that I created the below which works nicely:

VarVehicleVo2 = :Name( "Mean(Mean(VO2-[ml/min]))" )[Data Table( "GroupAvg" ) <<
Get rows Where( Contains( :Treatment, "C" ))];

Animal No.Avg << New Column( "VO2 absolute relative (%)",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			Local( {t0 = VarVehicleVo2()},
				(:Name( "Mean(Mean(VO2-[ml/min]))" ) / t0) * 100
			)
		)
	);

Now I have to do the same again, only now the data table (GroupAvgSEM) contains days too and the relative values should be calculated for each day (-6 up to 30). 

Of course, I can do the same as above and repeat it for all days, thereby creating a very long script with a local variable for each day but that seems a bit extreme.

I was looking into subsetting the Treatment C data and splitting it (on days) in order to loop through it inside the formula:

VarTable = GroupAvgSEM << select where(
	Contains( :Treatment, "ehicle" )) << subset( 
		output table name( "VarTable" ), selected rows( 1 ), selected columns( 0 ));

VarVO2 = VarTable << split(
			Output table name("VarVO2"),
			Split By( :Day no ),
			Split( :Name( "Mean(Mean(VO2-[ml/min]))" ) ),
			Remaining Columns( Drop All ),
			Sort by Column Property
		);


Animal No.Avg << New Column("VO2 absolute relative (%)",
    Numeric,     Formula(
		Local( {//loop through VarVO2 column values},
			(:Name( "Mean(Mean(VO2-[ml/min]))" ) / /*VarVO2 column values*/) * 100
		))
	);

Is that possible or should I go about this a different way?

Br Julie

[#2 · Accepted solution]

Try this

dt = Data Table("Animal No.Avg");
tb = dt <<
Tabulate(
	Show Control Panel( 0 ),
	Add Table(
		Column Table(
			Analysis Columns( :Name( "Mean(VO2-[ml/min])" ) ),
			Statistics( Mean )
		),
		Row Table( Grouping Columns( :Day no ) )
	),
	Local Data Filter(
		Add Filter( columns( :Treatment ), Where( :Treatment == "C" ) )
	)
);
dt1 = tb << make into data table;

dt2 = dt << Join(
	With( dt1 ),
	Select(
		:Treatment,
		:Animal No.,
		:Day no,
		:N Rows,
		:Name( "Mean(VO2-[ml/min])" ),
		:Name( "Mean(RQ)" ),
		:Name( "Mean(EE-[kcal/h])" )
	),
	SelectWith( :Name( "Mean(Mean(VO2-[ml/min]))" ) ),
	By Matching Columns( :Day no = :Day no ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 )
);

dt2 << New Column("Relative VO2",
	Formula(:Name( "Mean(VO2-[ml/min])" )/:Name( "Mean(Mean(VO2-[ml/min]))" ) * 100)
);

tb << Close Window;

close(dt1, NoSave);