topic Re: Automated reports - selecting variable number of columns in Discussions

Automated reports - selecting variable number of columns

brammern — Fri, 09 Jun 2023 16:06:48 GMT

I know that this is a pretty basic request but I'm new to coding and got a bit stumped at the last huddle.

I've writing an add-in for our quality engineers to take data in one table, split the table, add spec and control limits from another table and then I want them to have the control charts to be created for them - they either just have a dialogue box to select columns or all numeric/continuous columns are plotted.

The issue is the names and the number of columns will vary each time as it's product dependent and I want a generic addin.

This is the last bit of code that is troubling me - I can get it to work with a prescribed number of columns (13 in this case) but want it to be any number. I know I need a couple of simple lines of code but just can't get my head around it. Any help appreciated.

/* Query the user for X and Y columns */
cdlg = Column Dialog(
yCol = ColList( "Y, Response", MinCol( 1 ), MaxCol( 25 ), Data Type( "Numeric" ) ),
xCol = ColList( "X, Factor", MinCol( 1 ), MaxCol( 1 ) )
);
/* Unload the values from the Column Dialog */
my_Y1 = cdlg["yCol"][1];
my_Y2 = cdlg["yCol"][2];
my_Y3 = cdlg["yCol"][3];
my_Y4 = cdlg["yCol"][4];
my_Y5 = cdlg["yCol"][5];
my_Y6 = cdlg["yCol"][6];
my_Y7 = cdlg["yCol"][7];
my_Y8 = cdlg["yCol"][8];
my_Y9 = cdlg["yCol"][9];
my_Y10 = cdlg["yCol"][10];
my_Y11 = cdlg["yCol"][11];
my_Y12 = cdlg["yCol"][12];
my_Y13 = cdlg["yCol"][13];
my_X = cdlg["xCol"][1];
/* Use the column selections in an analysis */
Control Chart Builder(
	Show Two Shewhart Charts( 0 ),
	Sort by Row Order( 1 ),
	Connect Thru Missing( 1 ),
	Variables(
		Subgroup(my_X ),
		Y(my_Y1,my_Y2,my_Y3,my_Y4,my_Y5,my_Y6,my_Y7,my_Y8,my_Y9,my_Y10,my_Y11,my_Y12,my_Y13)),
	Chart(
		Points( Statistic( "Individual" ) ),
		Limits( Sigma( "Levey Jennings" ) ),
		Process Capability Analysis(
			Within Sigma Capability( 0 ),
			Histogram( 1, Show Within Sigma Density( 0 ) )
		)
	));

Re: Automated reports - selecting variable number of columns

txnelson — Thu, 30 Mar 2023 18:25:21 GMT

The issues that you need to solve are dealing with an unknown number of Y columns, and then how to insert that unknown number of Y columns into the Control Chart Builder. Below is one way to do that

/* Query the user for X and Y columns */
cdlg = Column Dialog(
	yCol = ColList( "Y, Response", MinCol( 1 ), MaxCol( 25 ), Data Type( "Numeric" ) ),
	xCol = ColList( "X, Factor", MinCol( 1 ), MaxCol( 1 ) )
);

// There isn't a need to unload the values from the Column Dialog
/* Unload the values from the Column Dialog */
/*my_Y1 = cdlg["yCol"][1];
my_Y2 = cdlg["yCol"][2];
my_Y3 = cdlg["yCol"][3];
my_Y4 = cdlg["yCol"][4];
my_Y5 = cdlg["yCol"][5];
my_Y6 = cdlg["yCol"][6];
my_Y7 = cdlg["yCol"][7];
my_Y8 = cdlg["yCol"][8];
my_Y9 = cdlg["yCol"][9];
my_Y10 = cdlg["yCol"][10];
my_Y11 = cdlg["yCol"][11];
my_Y12 = cdlg["yCol"][12];
my_Y13 = cdlg["yCol"][13];
my_X = cdlg["xCol"][1];*/

// What is returned from the ColDialog for yCol is a list of columns in the 
// form of expressions, not strings.  Control Chart builder does not seem to
// like processing of lists of a length greater than 1.  So to get around that
// I take the list and convert it into a single character string.  I have found
// the fastest way to do this is to strip off the "{}" from the list leaving
// everything inside of the list as a single string 
temp = Substr( Char( cdlg["yCol"] ), 2, Length( Char( cdlg["yCol"] ) ) - 2 );

/* Use the column selections in an analysis */
// Build a character string that is the complete command string
// to run the Control Chart Builder, inserting the above created 
// temp variable and then run it. 
Eval(
	Parse(
		"Control Chart Builder(
	Show Two Shewhart Charts( 0 ),
	Sort by Row Order( 1 ),
	Connect Thru Missing( 1 ),
	Variables(
		Subgroup(eval(cdlg[\!"xCol\!"])),
		Y("
		 || temp ||
		")),
	Chart(
		Points( Statistic( \!"Individual\!" ) ),
		Limits( Sigma( \!"Levey Jennings\!" ) ),
		Process Capability Analysis(
			Within Sigma Capability( 0 ),
			Histogram( 1, Show Within Sigma Density( 0 ) )
		)
	));"
	)
);
	
	
;

Re: Automated reports - selecting variable number of columns

brammern — Fri, 31 Mar 2023 07:36:15 GMT

Thanks - that's brilliant.

I'd never have got there myself