Subscribe Bookmark RSS Feed

Manipulating and Combining Control Chart Windows

rfeick

Community Trekker

Joined:

Sep 30, 2016

I have a script that pulls data from excel and creates control charts from it. The data corresponds to readings from different locations and I want to be able to display the readings from RM 16 and RM 25 in the same window, save a jpg of the window with both plots, and close the individual plots for each location. I have figured out how to combine the control charts, but cannot seem to get the new window to save as a jpg or how to close the individual control charts since I don't need those files after they've been combined. Is there a way to do this? I've attached the script I have so far. I'm trying to keep the script as flexible as possible in case we ever need to add more locations to process data from.

RM = {RM 16, RM 25, Rm.119, Rm.124, Rm 152 153 155};

For(r=1, r<=N Items(RM), r++,
CY = Open("FILEPATH HERE".xlsx",
			Worksheets( RM[r] ),
			Use for all sheets( 1 ),
			Concatenate Worksheets( 0 ),
			Create Concatenation Column( 0 ),
			Worksheet Settings(
				1,
				Has Column Headers( 1 ),
				Number of Rows in Headers( 1 ),
				Headers Start on Row( 1 ),
				Data Starts on Row( 2 ),
				Data Starts on Column( 1 ),
				Data Ends on Row( 0 ),
				Data Ends on Column( 3 ),
				Replicated Spanned Rows( 1 ),
				Replicated Spanned Headers( 0 ),
				Suppress Hidden Rows( 1 ),
				Suppress Hidden Columns( 1 ),
				Suppress Empty Columns( 1 ),
				Treat as Hierarchy( 0 ),
				Multiple Series Stack( 0 ),
				Import Cell Colors( 0 ),
				Limit Column Detect( 0 ),
				Column Separator String( "-" )
			)
		);
//what if CY has no data yet? script will stop unless you delete room from RM
CY << Set Name(char(RM[r]) || " " ||char(Year(Today())));
CY << Select Where(Is Missing(:Date))<<Delete Rows;

CB = Open("FILEPATH HERE".xlsx",
			Worksheets( RM[r] ),
			Use for all sheets( 1 ),
			Concatenate Worksheets( 0 ),
			Create Concatenation Column( 0 ),
			Worksheet Settings(
				1,
				Has Column Headers( 1 ),
				Number of Rows in Headers( 1 ),
				Headers Start on Row( 1 ),
				Data Starts on Row( 2 ),
				Data Starts on Column( 1 ),
				Data Ends on Row( 0 ),
				Data Ends on Column( 3 ),
				Replicated Spanned Rows( 1 ),
				Replicated Spanned Headers( 0 ),
				Suppress Hidden Rows( 1 ),
				Suppress Hidden Columns( 1 ),
				Suppress Empty Columns( 1 ),
				Treat as Hierarchy( 0 ),
				Multiple Series Stack( 0 ),
				Import Cell Colors( 0 ),
				Limit Column Detect( 0 ),
				Column Separator String( "-" )
			)
		);

column(1) << Data Type(Numeric) << Model Type(Continuous) <<Format( "ddMonyyyy", 12 )<<Input Format( "ddMonyyyy" );
CB << Set Name(char(RM[r]) || " " ||char(Year(Today())-1));
CB << Select Where(Is Missing(:Date))<<Delete Rows;

CB << Concatenate(CY, Append to first table);
Close(CY);
CB << Set Name(char(RM[r]) || " " || char(Year(Today())-1)||"-"||char(Year(Today())));

:Room Location # << Data Type(Numeric);
:Room Location # << Set Property ("Row Order Levels", 1); 

CB << Sort(Replace table, By(Room Location#), Order(Ascending));

CB << Save("C:\Users\rfeick\Desktop\Air Particle Data\JMP Control Chart Generated Files\"|| char(RM[r]) || " " || char(Year(Today())-1)||"-"||char(Year(Today()))|| ".jmp");

CB << Select Where(:Date >= As Date(Today()-365*86400))<<Invert Row Selection<<Hide and Exclude;

IF(r==1, 
C1 = Control Chart Builder(
	Size( 1200, 550 ),
	Show Two Shewhart Charts( 0 ),
	Show Control Panel( 0 ),
	Show Capability( 0 ),
	Variables( 
		Y( :Name( "0.5 Micron Particulate (part/m3)" ) ),
		Phase(:Room Location #)
	),
	Chart( Points( Statistic( "Individual" ) ), Limits( Sigma ) ),
	SendToReport(
		Dispatch(
			{},
			"Control Chart Builder",
			OutlineBox,
			{Set Title( "IR Control  Chart" )}
		),
		Dispatch(
			{},
			"0.5 Micron Particulate (part",
			ScaleBox,
			{Min( 0 ), Max( 80000 ), Inc( 5000 ), Minor Ticks( 0 ),
			Add Ref Line( 62500, "Dashed", "Purple", "125000", 1 ),
			Add Ref Line( 352000, "DashDotDot", "Cyan", "352000", 1 )}
		),
		Dispatch(
			{},
			"graph display 1 title",
			TextEditBox,
			{Set Text(
				char(RM[r]) ||" 0.5 Micron Particulate (part/m3) "|| char(As Date(:Date[1]))|| " - " || char(As Date(:Date[NRows()]))
			)}
		)
	)
);

Report(C1)[AxisBox(1)] <<Delete;

Report(C1) << Save Picture("FILEPATH HERE\Air Particulate CC " ||char(RM[r]) || " " ||char(As Date(:Date[1]))|| " - " || char(As Date(:Date[NRows()]))|| ".jpg");

	
);

IF(r==2, 
C2 = Control Chart Builder(
	Size( 1200, 550 ),
	Show Two Shewhart Charts( 0 ),
	Show Control Panel( 0 ),
	Show Capability( 0 ),
	Variables( 
		Y( :Name( "0.5 Micron Particulate (part/m3)" ) ),
		Phase(:Room Location #)
	),
	Chart( Points( Statistic( "Individual" ) ), Limits( Sigma ) ),
	SendToReport(
		Dispatch(
			{},
			"Control Chart Builder",
			OutlineBox,
			{Set Title( "IR Control  Chart" )}
		),
		Dispatch(
			{},
			"0.5 Micron Particulate (part",
			ScaleBox,
			{Min( 0 ), Max( 400000 ), Inc( 50000 ), Minor Ticks( 0 ),
			Add Ref Line( 125000, "Dashed", "Purple", "125000", 1 ),
			Add Ref Line( 352000, "DashDotDot", "Cyan", "352000", 1 )}
		),
		Dispatch(
			{},
			"graph display 1 title",
			TextEditBox,
			{Set Text(
				char(RM[r]) ||" 0.5 Micron Particulate (part/m3) "|| char(As Date(:Date[1]))|| " - " || char(As Date(:Date[NRows()]))
			)}
		)
	)
);
Report(C2)[AxisBox(1)] <<Delete;

Report(C2) << Save Picture("FILEPATH HERE\Air Particulate CC " ||char(RM[r]) || " " ||char(As Date(:Date[1]))|| " - " || char(As Date(:Date[NRows()]))|| ".jpg");

	
);



If(r==3 | r==4,
C3 = Control Chart Builder(
	Size( 1200, 550 ),
	Show Two Shewhart Charts( 0 ),
	Show Control Panel( 0 ),
	Show Capability( 0 ),
	Variables( 
		Y( :Name( "0.5 Micron Particulate (part/m3)" ) ),
		Phase(:Room Location #)
	),
	Chart( Points( Statistic( "Individual" ) ), Limits( Sigma ) ),
	SendToReport(
		Dispatch(
			{},
			"Control Chart Builder",
			OutlineBox,
			{Set Title( "IR Control  Chart" )}
		),
		Dispatch(
			{},
			"0.5 Micron Particulate (part",
			ScaleBox,
			{Min( 0 ), Max( 80000 ), Inc( 5000 ), Minor Ticks( 0 ),
			Add Ref Line( 62500, "Dashed", "Purple", "125000", 1 ),
			Add Ref Line( 352000, "DashDotDot", "Cyan", "352000", 1 )}
		),
		Dispatch(
			{},
			"graph display 1 title",
			TextEditBox,
			{Set Text(
				char(RM[r]) ||" 0.5 Micron Particulate (part/m3) "|| char(As Date(:Date[1]))|| " - " || char(As Date(:Date[NRows()]))
			)}
		)
	)
);
Report(C3)[AxisBox(1)] <<Delete;

Report(C3) << Save Picture("FILEPATH HERE\Air Particulate CC " ||char(RM[r]) || " " ||char(As Date(:Date[1]))|| " - " || char(As Date(:Date[NRows()]))|| ".jpg");


	
);

IF(r==5,
C4 = Control Chart Builder(
	Size( 1200, 550 ),
	Show Two Shewhart Charts( 0 ),
	Show Control Panel( 0 ),
	Show Capability( 0 ),
	Variables( 
		Y( :Name( "0.5 Micron Particulate (part/m3)" ) ),
		Phase(:Room Location #)
	),
	Chart( Points( Statistic( "Individual" ) ), Limits( Sigma ) ),
	SendToReport(
		Dispatch(
			{},
			"Control Chart Builder",
			OutlineBox,
			{Set Title( "IR Control  Chart" )}
		),
		Dispatch(
			{},
			"0.5 Micron Particulate (part",
			ScaleBox,
			{Min( 0 ), Max( 400000 ), Inc( 50000 ), Minor Ticks( 0 ),
			Add Ref Line( 125000, "Dashed", "Purple", "125000", 1 ),
			Add Ref Line( 352000, "DashDotDot", "Cyan", "352000", 1 )}
		),
		Dispatch(
			{},
			"graph display 1 title",
			TextEditBox,
			{Set Text(
				char(RM[r]) ||" 0.5 Micron Particulate (part/m3) "|| char(As Date(:Date[1]))|| " - " || char(As Date(:Date[NRows()]))
			)}
		)
	)
);

Report(C4)[AxisBox(1)] <<Delete;

Report(C4) << Save Picture("FILEPATH HERE\Air Particulate CC " ||char(RM[r]) || " " ||char(As Date(:Date[1]))|| " - " || char(As Date(:Date[NRows()]))|| ".jpg");

	
);
);

Together = New Window("RM 16 & RM 25 0.5 Micron Particulate (part/m3) "|| char(As Date(:Date[1]))|| " - " || char(As Date(:Date[NRows()])), V List Box(Report(C1),Report(C2))); 

Together << Save Picture("FILEPATH HERE\Air Particulate CC RM 16 & RM 25 0.5 Micron Particulate (part/m3) "||char(As Date(:Date[1]))|| " - " || char(As Date(:Date[NRows()]))|| ".jpg");

C1 <<Close Window;
C2 << Close Window;

  

2 ACCEPTED SOLUTIONS

Accepted Solutions
ian_jmp

Staff

Joined:

Jun 23, 2011

Solution

I didn't look at all your code, but it seems that you might make things simpler by using the 'Concatenate Worksheets' option with 'Create Concatenation Column' when you read the data in (to make just a single table). Then you can exploit the fact that Control Chart Builder will use a 'By' variable. The code below shows the idea:

dt = Open("$SAMPLE_DATA/Big Class.jmp");
win = NewWindow("Combined Charts",
				dt << Control Chart Builder(
									Show Control Panel( 0 ),
									Show Capability( 0 ),
									Variables( Y( :height ) ),
									Chart( Position( 1 ), Limits( Sigma( Moving Range ) ) ),
									Chart( Position( 2 ), Limits( Sigma( Moving Range ) ) ),
									By( :sex )
								)
				);
win << savePicture("$DESKTOP/My Charts.jpg", JPG);
win << closeWindow;

 

txnelson

Super User

Joined:

Jun 22, 2012

Solution
// Here is how to close the CY data table
close( cy, nosave );

// The window object does not support the Save Picture message, however a border box does
// so just include a Border Box in the window and then save it
Together = New Window(
	"RM 16 & RM 25 0.5 Micron Particulate (part/m3) " || Char( As Date( :Date[1] ) ) || " - " ||
	Char( As Date( :Date[N Rows()] ) ),
	TheBorder = Border Box(
	V List Box( Report( C1 ), Report( C2 ) ))
); 

TheBorder << Save Picture(
	"FILEPATH HERE\Air Particulate CC RM 16 & RM 25 0.5 Micron Particulate (part/m3) " ||
	Char( As Date( :Date[1] ) ) || " - " || Char( As Date( :Date[N Rows()] ) ) || ".jpg"
);
Jim
2 REPLIES
ian_jmp

Staff

Joined:

Jun 23, 2011

Solution

I didn't look at all your code, but it seems that you might make things simpler by using the 'Concatenate Worksheets' option with 'Create Concatenation Column' when you read the data in (to make just a single table). Then you can exploit the fact that Control Chart Builder will use a 'By' variable. The code below shows the idea:

dt = Open("$SAMPLE_DATA/Big Class.jmp");
win = NewWindow("Combined Charts",
				dt << Control Chart Builder(
									Show Control Panel( 0 ),
									Show Capability( 0 ),
									Variables( Y( :height ) ),
									Chart( Position( 1 ), Limits( Sigma( Moving Range ) ) ),
									Chart( Position( 2 ), Limits( Sigma( Moving Range ) ) ),
									By( :sex )
								)
				);
win << savePicture("$DESKTOP/My Charts.jpg", JPG);
win << closeWindow;

 

txnelson

Super User

Joined:

Jun 22, 2012

Solution
// Here is how to close the CY data table
close( cy, nosave );

// The window object does not support the Save Picture message, however a border box does
// so just include a Border Box in the window and then save it
Together = New Window(
	"RM 16 & RM 25 0.5 Micron Particulate (part/m3) " || Char( As Date( :Date[1] ) ) || " - " ||
	Char( As Date( :Date[N Rows()] ) ),
	TheBorder = Border Box(
	V List Box( Report( C1 ), Report( C2 ) ))
); 

TheBorder << Save Picture(
	"FILEPATH HERE\Air Particulate CC RM 16 & RM 25 0.5 Micron Particulate (part/m3) " ||
	Char( As Date( :Date[1] ) ) || " - " || Char( As Date( :Date[N Rows()] ) ) || ".jpg"
);
Jim