66206_en-US

liliconcom

Control Charts in a Tabbox loop

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. 

 

Re: Control Charts in a Tabbox loop

Sorry, it's because the original script had both cols and Rcols in the script (which were the same).  I deleted cols but forgot to update the part of the script where it builds the list of control chart columns.  I still had cols in memory, so it worked then.  I will update the script in my previous reply to fix that.

Re: Control Charts in a Tabbox loop

Wow, this is great I appreciate all the renovations as well thank you. I do receive an error when I try and run this that I am unsure of what it means hopefully you can help me understand it. 

 

Error 

Subscript Range in access or evaluation of 'Subscript' , cols[/*###*/j]

Re: Control Charts in a Tabbox loop

[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.

 

Re: Control Charts in a Tabbox loop

Yeah of course, here is a data table that I scrubbed names off. 

Re: Control Charts in a Tabbox loop

Can you post a table (sanitize it if needed) we can use with your script?

First thing I notice is "<< get kets".  Is that supposed to be "get keys"?  If so, you need to use that with an array, not a data table.  I'm guessing you just want a list of all the unique values of GROUPING.  You can do that with this:

id = Associative Array(dt_final:GROUPING << Get Values) << Get Keys;

There's a lot more I see in your script, but I'm not going to attempt to debug it without a table to work with.