Hi,
Kind of a newbie with JMP Scripting. Appreciate any help that I can get.
Here are the steps that I'm trying to achieve. First I'm opening the excel file and within that excel file selecting a sheet. Then I'm applying few SQL statements to get a subset of the sheet;s data and naming that data set
as Subset of Adherence. Following that is creation of log to which I'm writing all the control points which have failed. Next is to chart out a control chart based on the Final CPASU column. Here is where my problem begins.
I want the control chart to be drawn only if the data has failed any of the tests. In other words if the data points pass all the seven tests of control limits I don't want JMP to display that chart. It should only display the chart when something is out of limits. This is particularly useful when I have more than 50 charts which is the case here but I have not included all the other 49 charts code.
Doing that would ensure I'm only focusing on correcting things that are going wrong than looking at all charts a
nd figuring out which charts data points went wrong. Finally what I'm doing is saving these charts as a HTML file in a folder so I can write a script in VBA which would then post these images on sharepoint site. This step is working fine but I'm falling short of ideas with the above step.
My code follows:
Open Database("DSN=Excel Files;DBQ=C:\Users\XXXXX\Documents\XXXX\JMP Analysis\XXXXX.xlsm;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;", "SELECT * FROM `'CPASU$'`","CPASU$");
//Create the subset
(Data Table( "CPASU by queue$" ) << Select Where(AND(FWEEK>=201201,FWEEK!=201248, FWEEK!=201247, FWEEK!=201243, Adherence>0))<< Subset( Linked ) ;
Current Data Table( Data Table( "Subset of Adherence $" ) ));
//Clearing the log and setup columns for csv file
Clear log();
Write("Queue Name,Test,Column,Data Point\!N");
//Begin Control Charts
New Window( "Subset of CPASU",
V List Box(
Control Chart(Sample Label( :FWEEK ),(Alarm Script(Write("NA Standard,",qc_test,",",qc_col,qc_sample,"\!N")),
Group Size( 1 ),
KSigma( 3 ),}
Chart Col( :Final CPASU, Individual Measurement(Shade Zones(1), Test 1(1)) ),Where( :Queue Name == "NA Standard" ),),);
Savelog("C:\Users\XXXX\Documents\XXXX\JMP Analysis\JMPlogs.csv");
Window(“Subset of CPASU”) << SaveHTML (“C:\Users\XXX\Documents\XXX\JMP Analysis\HTML\CPASU.html”);
Please ignore if there is a mismatch in the number of opening and closing brackets.