Control Charts in a Tabbox loop

CharlesIII

Hello, I am working on an automated script for outputting control charts based on a list of parameters. I have a stacked data table like below

 

 

 

which I am trying to generate control charts from. As far as the scripting goes I have this so far

 

Rcols = dt_final << Get Column Names();
id = dt_final << get kets;
tb = tabbox();

for(i, i<=nitems(id), i++,
	cols = Rcols;
	dt =  dt_final << subset(by(id[i]));
	dt << sort(by(dt, :DATE_TIME_START), Order(Ascending), Replace Table);
	ccExpr = Expr(Control Chart(
		Sample Label( column(dt,:DATE_TIME_START) ),
		KSigma( 3 ),	
		
	For( i = 1, i <= N Items( cols ), i++,
		insert into(ccExpr, EvalExpr(Chart Col( Column( dt_final, Expr(cols[i]) ), Levey Jennings )))
	);
	cc = Eval(EvalExpr(
		platform(dt_final, 
			Expr(
				nameexpr(ccExpr)
			)
		)
	));
	
	);
	tb << Append(group, cc);
	// Create the display window

));

nw = New Window( "Control Charts", Outlinebox("Control Charts By Process ID",  tb));

I know I am probably off of the best way of going about this but this is ultimately what i need to do:

 

1. Subset dt_final by the grouping 
2. change the date column to numeric -> continuous -> time -> m/d/y h:m (just for uniformity)
3. sort the subsets by date_time_start
4. generate levey jennings control charts for each column and append it to the tabbox
5. and since not every group has all the columns delete the charts with no data

There are a few steps still missing in here as far as like changing the date data type, however, if someone could give me some direction on this loop. 

 

Thanks. 

 

cwillden

[UPDATE] Fixed a bug in the script.

 

This seems to meet all your criteria, but is a bit of different approach that I think you'll find a lot cleaner in the end because you don't create a whole bunch of tables for each ID.  Instead of subsetting the table, you use a Where() clause for the control charts on each tab to subset the data for each ID.

 

dt_final = Current Data Table();

//Sort by Grouping and Start Time
dt_final << Sort(
 By( :GROUPING, :DATE_TIME_START ),
 Order( Ascending, Ascending ),
 Output Table( "Final Sorted" ),
 Replace Table
);

//List of Columns and Individual IDs
Rcols = dt_final << Get Column Names();
id = Associative Array(dt_final:GROUPING) << get keys;

//Initialize the window
nw = New Window("Control Charts",
 Outlinebox("Control Charts By Process ID",
 tb = tabbox();
 )
);

//Create expression for control charts - use same expression for each subset of the data table
//We'll substitute different values for GroupID and col_list for each grouping
ccExpr = "Control Chart(
 Sample Label( :DATE_TIME_START ),
 KSigma( 3 ), col_list, Where(:GROUPING == \!"GroupID\!"))"; 

//Loop through each ID and add control charts to a new tab
for(i=1, i <= N Items(id), i++,
 //If column has data, add it to the control chart report
 cols_temp = ""; //will hold list of control charts for the current ID

 //Loop through each column, determine if column has data for current ID
 For( j = 3, j <= N Items( Rcols ), j++,
 If(Sum(!Is Missing(Column(dt_final, j)[dt_final << Get Rows Where(:GROUPING == id[i])])) > 0,
 cols_temp = cols_temp || Eval Insert("\[, Chart Col( Column( "^Rcols[j]^" ), Levey Jennings )]\");
 )
 );

 //Substitute in the current ID for GroupID in ccExpr
 ccExpr_temp = Substitute(ccExpr, "GroupID", id[i]);

 //Substitute in the list of control chart columns for col_list in ccExpr
 ccExpr_temp = Substitute(ccExpr_temp, "col_list", cols_temp);

 //Add results to the tab box by parsing the string ccExpr_temp into an expression and evaluating it
 tb << Add(id[i], platform(dt_final, Eval(Parse(ccExpr_temp))));
);

There's a lot to explain in here, so just let me know what you have questions about.