Scrambled control limits from a reference file

Question

My script was functioning fine before I added a new phase with updated limits to the control limits file. Now the limits are scrambled. I've quadruple checked the control limits file to make sure they are in the correct order. Any idea what could be causing the mix-up? I am experiencing this same issue across multiple product charts. I've tried assigning Value Ordering and turning on Row Order Levels, neither have worked.

dt_limits = Open( "R:\Research\PS5\Control_Limits\PMC013\PMC013 Metrology CLs Review.jmp" );
dt_limits << Sort( By( :Quarter ), Order( Ascending ), Replace Table );
dt = Open( "R:\Research\PS5\Control_Limits\JMP SPC files\PMC013 SPC Charts.jmp" );

In the attached picture, the limits for phase "4 2015" belong to phase "3 2018"

ambrown06 · Answer

I have tried both of your solutions. The second solution created a chart with JMP created limits instead of using the limits file. The summarize designation didn't make a noticable difference.

I've "fixed" this issue in other charts by either assigning/removing value ordering and/or row order levels, but this one still remains scrambled.

Going line by line, the limits are applied in the order: 1, 1 2016, 2 2015, 2 2017,  and so on.

gzmorgan0 · Answer

Your code looks good. If a Metrology[i] had one or more missing quarters  the For j counting loop could be off.

Have you used the debugger?

The only changes I might make is to specify the table  x for summarize

summarize( yTables[i], phaselevels=By(:Quarter) )

Also instead of counting the phaselimits in the "For j -loop", I might use code to match the limits,

dt_limits << Sort( By( :Quarter, :_LimitsKey ), Order( Ascending, Ascending ), Replace Table );

//other code not shown

For( j = 1, j <= N Items( phaseLevels ), j++,
	      km = dt_limits << get rows where(:Quarter == phaseLevels[j] & :_LimitsKey=="_Mean");
	      kl = dt_limits << get rows where(:Quarter == phaseLevels[j] & :_LimitsKey=="_LCL");
	      ku = dt_limits << get rows where(:Quarter == phaseLevels[j] & :_LimitsKey=="_UCL");
	      ks = dt_limits << get rows where(:Quarter == phaseLevels[j] & :_LimitsKey=="_Std Dev");

Insert Into(
			imExpr,
			Eval Expr(

Phase Level(
					Expr( phaseLevels[j] ),
					Sigma( Expr( sigma[ks] ) ),
					Avg( Expr( avg[km] ) ),
					LCL( Expr( lcl[kl] ) ),
					UCL( Expr( ucl[ku] ) )

)
			)
		)
	);

Hope that helps.

ambrown06 · Answer

Here's the full script minus the row marking at the beginning:

dt_limits = Open( "R:\Research\PS5\Control_Limits\PMC013\PMC013 Metrology CLs Review.jmp" );
dt_limits << Sort( By( :Quarter ), Order( Ascending ), Replace Table );
dt = Open( "R:\Research\PS5\Control_Limits\JMP SPC files\PMC013 SPC Charts.jmp" );
Metrology = dt_limits << get column names( numeric, continuous, string );
 
yTables = {};
rpt = New Window( "SPC",
	<<OnClose(
		For( i = 1, i <= N Items( yTables ), i++,
			Close( yTables[i], NoSave )
 
		)
	),
	vlb = V List Box()
 
);
sigmaRows = dt_limits << Get Rows Where( :_LimitsKey == "_Std Dev" );
avgRows = dt_limits << Get Rows Where( :_LimitsKey == "_Mean" );
uclRows = dt_limits << Get Rows Where( :_LimitsKey == "_UCL" );
lclRows = dt_limits << Get Rows Where( :_LimitsKey == "_LCL" );
For( i = 1, i <= N Items( Metrology ), i++,
	r = dt << get Rows where( !Is Missing( Column( Metrology[i] )[Row()] ) );
	yTables[i] = dt << Subset(
		Link to original data table( 1 ), 
 
		Rows( r ),
		columns( :Quarter, :Lot Number, As Name( Metrology[i] ) ),
		"private"
 
	);
	Summarize( phaseLevels = By( :Quarter ) );
	emptyLoc = Contains( phaseLevels, "" );
	If( emptyLoc,
		Remove From( phaseLevels, emptyLoc )
 
	);
	cc = Expr(
		Control Chart( Phase( :Quarter ), Sample Label( :Lot Number ), KSigma( 3 ) )
 
	);
	xmin = 1;
	incx = 1;
	framex = 730;
	framey = 302;
	frame2x = 25;
	frame2y = 302;
	SendReport = Expr(
 
		SendtoReport(
			Dispatch(
				ytables,
				"1", 
 
				ScaleBox,
				{Min( xmin ), Max(), Inc( incx ), MInor Ticks( 0 )}
 
			),
			Dispatch(
				ytables,
				"IR Chart of IM", 
 
				FrameBox,
				{Frame Size( framex, framey )}
			),
			Dispatch(
				ytables,
				"IR Chart of IM",
				FrameBox( 2 ), 
 
				{Frame Size( frame2x, frame2y )}
			)
		)
	);
	sigma = Column( dt_limits, Metrology[i] )[sigmaRows];
	avg = Column( dt_limits, Metrology[i] )[avgRows];
	ucl = Column( dt_limits, Metrology[i] )[uclRows];
	lcl = Column( dt_limits, Metrology[i] )[lclRows];
	imExpr = Expr(
 
		Individual Measurement()
	);
	chCol = Expr( Chart Col() );
	For( j = 1, j <= N Items( phaseLevels ), j++,
		Insert Into(
 
			imExpr,
			Eval Expr(
 
				Phase Level(
					Expr( phaseLevels[j] ),
					Sigma( Expr( sigma[j] ) ),
					Avg( Expr( avg[j] ) ),
					LCL( Expr( lcl[j] ) ),
					UCL( Expr( ucl[j] ) )
 
				)
			)
		)
	);
	Insert Into( chCol, Column( dt_limits, Metrology[i] ) );
	Insert Into( chCol, Name Expr( imExpr ) );
	Insert Into( cc, Name Expr( chCol ) );
	Insert Into( cc, Name Expr( SendReport ) );
	vlb << Append( Eval( Eval Expr( yTables[i] << Expr( Name Expr( cc ) ) ) ) );
 
);
Current Data Table( dt );
dt_limits << close window;

The samples are labeled by lot number. The entire table is sorted in ascending order by Quarter and then Lot Number before the above script is ran. I've attached a picture of the control limits file.

The only sort options I have are ascending and descending, unless I'm missing something.