803603_en-US

liliconcom

Need help with Column formula

Hi,

 

I want to convert this loop into an equivalent column formula

Names Default To Here( 1 );
dt = Current Data Table();
// Assuming Column B is column 2
testColumn = Column( dt, 1 );
llCol = dt << New Column( "LL Limit_7.5", Numeric );
ulCol = dt << New Column( "UL Limit_7.5", Numeric );
// Loop through each row starting from row 20
For( i = 20, i <= N Rows( dt ), i++,
	data = testColumn[1 :: i]; // Get values from row 20 to the current row
	q1 = Quantile( 0.25, data ); // 1st Quartile
	q3 = Quantile( 0.75, data ); // 3rd Quartile
	// Factor value is 6
	factorValue = 7.5;
   // Calculate ll and ul
	llx = q1 - (q3 - q1) * (factorValue - 0.675) / 1.35;
	ulx = q3 + (q3 - q1) * (factorValue - 0.675) / 1.35;
   // Set the calculated values in the new columns
	llCol[i] = llx;
	ulCol[i] = ulx;
);

I tried but it doesn't seem to be correct. Any advice?

newColExpr = Expr(
	llCol << Set Formula(
		If( Row() >= 20,
			Col Quantile( 0.25, Expr( TestColumn( 1, Row() ) ) ) - (Col Quantile( 0.75, Expr( TestColumn( 1, Row() ) ) )
			-Col Quantile( 0.25, Expr( TestColumn( 1, Row() ) ) )) * (Expr( factorValue ) - 0.675) / 1.35,
			.
		)
	);
	ulCol << Set Formula(
		If( Row() >= 20,
			Col Quantile( 0.75, Expr( TestColumn( 1, Row() ) ) ) + (Col Quantile( 0.75, Expr( TestColumn( 1, Row() ) ) )
			-Col Quantile( 0.25, Expr( TestColumn( 1, Row() ) ) )) * (Expr( factorValue ) - 0.675) / 1.35,
			.
		)
	);
);
Eval( Eval Expr( newColExpr ) );

Automation and ScriptingRe: Need help with Column formula

Could you explain what you wish to do?

Re: Need help with Column formula

Calculating PAT limits 

Re: Need help with Column formula

You can do something like this but I'm not exactly sure if it is the best way to go with this

Names Default To Here(1);

dt = Open("$DOWNLOADS/data_table.jmp");


dt << New Column("Low", Numeric, Continuous, Formula(
	As Constant(
		factorValue = 7.5;
	);
	If(Row() < 20,
		.
	,
		m = :Data[1::Row()];
		q1 = Quantile(0.25, m);
		q3 = Quantile(0.75, m);
		q1 - (q3 - q1) * (factorValue - 0.675) / 1.35;
	);
));

dt << New Column("High", Numeric, Continuous, Formula(
	As Constant(
		factorValue = 7.5;
	);
	If(Row() < 20,
		.
	,
		m = :Data[1::Row()];
		q1 = Quantile(0.25, m);
		q3 = Quantile(0.75, m);
		q3 + (q3 - q1) * (factorValue - 0.675) / 1.35;
	);
));

Re: Need help with Column formula

Interesting, for Col Quantiles, it's  first the column, then the Quantile.

 

Besides that, Col Quantile will take all rows - not rows 1::currentRow
-> will result in a constant value.

 

 

 

 

newColExpr = Expr(
	new column("llCol" ,Formula(
		If( Row() >= 20,
			Col Quantile( 0.25, Expr( TestColumn( 1, Row() ) ) ) - (Col Quantile( 0.75, Expr( TestColumn( 1, Row() ) ) )
			-Col Quantile( 0.25, Expr( TestColumn( 1, Row() ) ) )) * (Expr( factorValue ) - 0.675) / 1.35,
			.
		)
	));
	new column("ulCol" ,  Formula(
		If( Row() >= 20,
			Col Quantile( 0.75, Expr( TestColumn( 1, Row() ) ) ) + (Col Quantile( 0.75, Expr( TestColumn( 1, Row() ) ) )
			-Col Quantile( 0.25, Expr( TestColumn( 1, Row() ) ) )) * (Expr( factorValue ) - 0.675) / 1.35,
			.
		)
	));
);
 Eval(Eval Expr( newColExpr )) ;