topic Re: Using FORMULA for pasting the alues from one column based on the other column using IF statement in Discussions

Using FORMULA for pasting the alues from one column based on the other column using IF statement

AlphaPanda86751 — Sat, 10 Jun 2023 23:54:26 GMT

I am trying to fill the column of Slopes_1 and Intercept_1 (1000 rows) from Slopes and Intercept (36 rows), respectively based on if Cycle 2(1000 rows) matches Cycle 3 (36 rows)

Can you suggest a formula to be inputted in Slopes_1 and Intercept_1 ?

Re: Using FORMULA for pasting the alues from one column based on the other column using IF statement

ih — Wed, 21 Sep 2022 13:57:40 GMT

You could do something like this:

If( Row() == 1, :Slopes, :Cycle 2 == Lag( :Cycle 2 ), Lag( :Slopes_1 ), :Slopes )

As an example:

Names Default To Here( 1 );

dt = Open( "$Sample_data/big class.jmp" );

dt << New Column( "height_1",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( If( Row() == 1, :height, :age == Lag( :age ), Lag( :height_1 ), :height ) )
);

Re: Using FORMULA for pasting the alues from one column based on the other column using IF statement

jthi — Wed, 21 Sep 2022 15:12:10 GMT

There are many ways of doing this, here is one using associative arrays:

Names Default To Here(1);

// Sample data
dt = New Table("Untitled",
	Add Rows(9),
	New Column("C3", Numeric, "Continuous", Format("Best", 12), Set Values([1, 2, 3, ., ., ., ., ., .])),
	New Column("S",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([0.11, 0.12, 0.13, ., ., ., ., ., .])
	),
	New Column("I",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([1.1, 1.2, 1.3, ., ., ., ., ., .])
	),
	New Column("C2", Numeric, "Continuous", Format("Best", 12), Set Values([1, 1, 1, 2, 2, 2, 3, 3, 3])),
	New Column("S1", Numeric, "Continuous", Format("Best", 12), Set Values([., ., ., ., ., ., ., ., .])),
	New Column("I1", Numeric, "Continuous", Format("Best", 12), Set Values([., ., ., ., ., ., ., ., .]))
);

wait(1);
// Formulas start
Column(dt, "S1") << Formula(
	As Constant(
		aa_slope_c3 = Associative Array(Eval List(:c3 << get values), Eval List(:s << get values));
	);
	aa_slope_c3[:c2];
);

Column(dt, "I1") << Formula(
	As Constant(
		aa_intercept_c3 = Associative Array(Eval List(:c3 << get values), Eval List(:i << get values));
	);
	aa_intercept_c3[:c2];
);

// If data is always like this, can be simplified to
dt << New Column("ss", Numeric, Continuous, Formula(
	:S[:C2]
));
dt << New Column("ii", Numeric, Continuous, Formula(
	:I[:C2]
));

If you don't need a formula you could also use joins/virtual joins.

Formulas can be found from the script or from attached data table