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

Question

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 ?

jthi · Answer

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

547167.jmp

ih · Answer

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