Subscribe Bookmark RSS Feed

List Reference Issues

rfeick

Community Trekker

Joined:

Sep 30, 2016

I have a script that takes user input through a setup window and based on that info processes nonconformance cost data. I'm having issues with using the data from the input NC codes and dispositions (see attached picture or run the script) as part of the costcols function. The goal of the function is to remove any rows where the :DISP.TYPE is not what one of the checked disposition options and then for each entered code create a new column that multiples :NC.QTY * :COST. The script runs correctly except for that one function. The only thing I cna think of is there is some issue with how I set up or reference the different lists. There are two lists for the NC codes and two for the disposition. The first for each is the checkbox result to determine if action needs to be taken and the second contains the values to search for in the script. The codes are vairable, but the dispositions are hardcoded to correspond with the given checkboxes.

addcols = Function({},
CCT<<New Column("Month", Character, Nominal, Formula(Char(
	If( Month( :DETECT.DATE ) == 1,
		"January",
		If( Month( :DETECT.DATE ) == 2,
			"February",
			If( Month( :DETECT.DATE ) == 3,
				"March",
				If( Month( :DETECT.DATE ) == 4,
					"April",
					If( Month( :DETECT.DATE ) == 5,
						"May",
						If( Month( :DETECT.DATE ) == 6,
							"June",
							If( Month( :DETECT.DATE ) == 7,
								"July",
								If( Month( :DETECT.DATE ) == 8,
									"August",
									If( Month( :DETECT.DATE ) == 9,
										"September",
										If( Month( :DETECT.DATE ) == 10,
											"October",
											If( Month( :DETECT.DATE ) == 11,
												"November",
												If( Month( :DETECT.DATE ) == 12,
													"December",
													"You did something wrong"
												)
											)
										)
									)
								)
							)
						)
					)
				)
			)
		)
	)
) || " " || char(Year(:DETECT.DATE)))
);

CCT<<New Column("Year", Character, Nominal, Formula(Char(Year(:DETECT.DATE))));

//CCT<<New Column("Quarter", Character, Nominal, Formula())	
);





combinetables = Function({},
  CCT = SXFC << Concatenate(
			SVFC,
			Output Table("Combined Cost Table");
		);
Close(SXFC, No Save);
Close(SVFC, No Save);
);


costcols = Function({},
	codechecks = {code1a, code2a, code3a, code4a, code5a};
	codes = {code1b, code2b, code3b, code4b, code5b};
	dispchecks = {disp1, disp2, disp3, disp4, disp5, disp6};
	disp = {"SCRAP-R", "SCRAP-DNR", "DEVIATE-MATERIAL", "DEVIATE-PROCESS", "DEVIATE-PRODUCT", "REWORK"};
	
	CCT<<Select Where(:DISP.TYPES == "NOTDEFINED" | :DISP.TYPES == "DEVIATE" | :DISP.TYPES == "REVIEWED & REVERSED" | :DISP.TYPES == "SCRAP");
	CCT<<Delete Rows;
	CCT<<Clear Select;


	For(d=1, d<=NItems(dispchecks), d++,
		If(dispchecks[d]==1, Continue());
		CCT<<Select Where(:DISP.TYPES == disp[d]);
		CCT<<Delete Rows;
		CCT<<Clear Select;	
		);
		
	
	For(c=1, c<=NItems(codechecks), c++,
		If(codechecks[c]==0, Continue());
		CCT<<New Column("Code " || codes[c] || " Cost", Numeric, Continuous, Format(Currency), Formula(If(:NC.CODES == codes[c], :NC.QTY * :Cost,0 )));
	);	
);

cut2017 = Function({},
 SVNC<<Select Where(Year(DETECT.DATE) != 2017);
 SVNC<<Delete Rows;
 SVNC<<Clear Select;	
);


datelimit = Function({},
CCT<<Select Where(num(sdate) < :DETECT.DATE <= num(edate));
CCT<<Invert Row Selection;
CCT<<Delete Rows;
CCT<<Set Name(char(sdate) || " to " || char(edate) || " Final Cost Table");
);


getdata = Function({},
		sdate = sdb<<Get Text;
		edate = edb<<Get Text;
		code1a = cb1a<<Get;
		code1b = cb1b<<Get Text;
		code2a = cb2a<<Get;
		code2b = cb2b<<Get Text;
		code3a = cb3a<<Get;
		code3b = cb3b<<Get Text;
		code4a = cb4a<<Get;
		code4b = cb4b<<Get Text;
		code5a = cb5a<<Get;
		code5b = cb5b<<Get Text;
		disp1 = db1<<Get;	
		disp2 = db2<<Get;	
		disp3 = db3<<Get;	
		disp4 = db4<<Get;	
		disp5 = db5<<Get;	
		disp6 = db6<<Get;	
		range1 = rb1<<Get;
		range2 = rb2<<Get;
		range3 = rb3<<Get;	
);

lookup = Function({},
 SXLT = Open("C:\Users\rfeick\Desktop\New New Cost Attempt\Source Files\2016 Product Cost Lookup Table.jmp");
 SXFC = SXNC << Join(
		With( SXLT ),
		By Matching Columns( :Part Number for Cost = :Part Number ),
		Drop multiples( 0, 0 ),
		Name( "Include non-matches" )(1, 0),
		Preserve main table order( 1 ),
		Output Table( "2016 Final Cost Table" )
	);
column(32)<<Set Name("Cost");
Close(SXNC, No Save);
Close(SXLT, No Save);

 SVLT = Open("C:\Users\rfeick\Desktop\New New Cost Attempt\Source Files\2017 Product Cost Lookup Table.jmp");
 SVFC = SVNC << Join(
		With( SVLT ),
		By Matching Columns( :Part Number for Cost = :Part Number ),
		Drop multiples( 0, 0 ),
		Name( "Include non-matches" )(1, 0),
		Preserve main table order( 1 ),
		Output Table( "2017 Final Cost Table" )
	);
column(32)<<Set Name("Cost");
Close(SVNC, No Save);
Close(SVLT, No Save);

);

numforcost = Function({},
Current Data Table(SXNC);
SXNC<<New Column("Part Number for Cost", Character, Nominal);
Current Data Table()<<Begin Data Update;
For Each Row(:Part Number for Cost =
 Match( :RPN,
		"APPLICATOR 12 X 85 (56)", "11193",
		"PAA TREATED MESH BAGS", "11226",
		"SADMB-10D","12123",
		"SADMB-10X15", "12124",
		"SADMB-15D", "11364",
		"SADMB-20X20","12125",
		"SBS-LAA: LYOPHILIZED, TWO-LAYER","10069",
		"SCS - LAA", "11362",
		"SDH-LAA","167",
		"SDH-LAA-SF","10894",
		"SEH-LAA-SF","11289",
		"SFH - LAA - DP - SF","11214",
		"SFH-LAA-LV","11590",
		"SHH-AAA-SF","11429",
		"SHH-LAA","11383",
		"SHH-LAA-TQ-28X28-SF","11125",
		"SHH-LAA-TQ-28X33-SF","11126",
		"SLH-AAA-PBD-SF","11699",
		"FOAM TAPE STRIPS 12 X 56","11211",
		"SDH-AAA-T-SF","10892",
		"SHH-AAA-L-SF","11239",
		:RPN);
);
Current Data Table()<<End Data Update;

Current Data Table(SVNC);
SVNC<< New Column("Part Number for Cost", Character, Nominal);
Current Data Table()<<Begin Data Update;
For Each Row(:Part Number for Cost =
 Match( :RPN,
		"APPLICATOR 12 X 85 (56)", "11193",
		"PAA TREATED MESH BAGS", "11226",
		"SADMB-10D","12123",
		"SADMB-10X15", "12124",
		"SADMB-15D", "11364",
		"SADMB-20X20","12125",
		"SBS-LAA: LYOPHILIZED, TWO-LAYER","10069",
		"SCS - LAA", "11362",
		"SDH-LAA","167",
		"SDH-LAA-SF","10894",
		"SEH-LAA-SF","11289",
		"SFH - LAA - DP - SF","11214",
		"SFH-LAA-LV","11590",
		"SHH-AAA-SF","11429",
		"SHH-LAA","11383",
		"SHH-LAA-TQ-28X28-SF","11125",
		"SHH-LAA-TQ-28X33-SF","11126",
		"SLH-AAA-PBD-SF","11699",
		"FOAM TAPE STRIPS 12 X 56","11211",
		"SDH-AAA-T-SF","10892",
		"SHH-AAA-L-SF","11239",
		:RPN);
);
Current Data Table()<<End Data Update;
);










openncfiles = Function({},
SXNC = Open("http://sis.cookbiotech.com/NC/NC_2016_data.txt", Import Settings(End Of Line(CRLF, CR, LF), End Of Field(Other("0x7c"), CSV(0)), Strip Quotes(1), Use Apostrophe as Quotation Mark(0), Scan Whole File(1), Treat empty columns as numeric(0), CompressNumericColumns(0), CompressCharacterColumns(0), CompressAllowListCheck(0), Labels(1), Column Names Start(1), Data Starts(2), Lines To Read("All"), Year Rule("20xx")));
	column ("ENTITY#")<<data type(character);
	column ("ENTITY#")<<modeling type(nominal);
	column ("DETECT.DEPT")<<data type(character);
	column ("DETECT.DEPT")<<modeling type(nominal);
	column ("NC.CODES")<<data type(character);
	column ("NC.CODES")<<modeling type(nominal);
	column ("ASSIGNED.DEPT")<<data type(character);
	column ("ASSIGNED.DEPT")<<modeling type(nominal);
	column ("NC.COORDINATOR")<<data type(character);
	column ("NC.COORDINATOR")<<modeling type(nominal);
	column ("DETECT.BY")<<data type(character);
	column ("DETECT.BY")<<modeling type(nominal);
	column ("NC.CLOSE.DATE")<<data type(numeric, format("m/d/y"));
	column ("NC.COORDINATOR.DATE")<<data type(numeric, format("m/d/y"));
	column ("DETECT.DATE")<<data type(numeric, format("m/d/y"));
SXNC<<New Column("WO Cleaned", Character, Nominal, Formula(Item( 1, Munger( :WO#, 0, "*", "," ), "," )));
SXNC<<Set Name("2016 NC Data");

SVNC = Open("http://sis.cookbiotech.com/NC/27_nonconformance.txt", Import Settings(End Of Line(CRLF, CR, LF), End Of Field(Other("0x7c"), CSV(0)), Strip Quotes(1), Use Apostrophe as Quotation Mark(0), Scan Whole File(1), Treat empty columns as numeric(0), CompressNumericColumns(0), CompressCharacterColumns(0), CompressAllowListCheck(0), Labels(1), Column Names Start(1), Data Starts(2), Lines To Read("All"), Year Rule("20xx")));
	column ("ENTITY#")<<data type(character);
	column ("ENTITY#")<<modeling type(nominal);
	column ("DETECT.DEPT")<<data type(character);
	column ("DETECT.DEPT")<<modeling type(nominal);
	column ("NC.CODES")<<data type(character);
	column ("NC.CODES")<<modeling type(nominal);
	column ("ASSIGNED.DEPT")<<data type(character);
	column ("ASSIGNED.DEPT")<<modeling type(nominal);
	column ("NC.COORDINATOR")<<data type(character);
	column ("NC.COORDINATOR")<<modeling type(nominal);
	column ("DETECT.BY")<<data type(character);
	column ("DETECT.BY")<<modeling type(nominal);
	column ("NC.CLOSE.DATE")<<data type(numeric, format("m/d/y"));
	column ("NC.COORDINATOR.DATE")<<data type(numeric, format("m/d/y"));
	column ("DETECT.DATE")<<data type(numeric, format("m/d/y"));
SVNC<<New Column("WO Cleaned", Character, Nominal, Formula(Item( 1, Munger( :WO#, 0, "*", "," ), "," )));
SVNC<<Set Name("2017 NC Data");
);



resetcheckbox = Function({},
		cb1a<<Set(1,0);
		cb2a<<Set(1,0);
		cb3a<<Set(1,0);
		cb4a<<Set(1,0);
		cb5a<<Set(1,0);
		db1<<Set(1,0);
		db2<<Set(1,0);
		db3<<Set(1,0);
		db4<<Set(1,0);
		db5<<Set(1,0);
		db6<<Set(1,0);
		rb1<<Set(1,0);
		rb2<<Set(1,0);
		rb3<<Set(1,0);
);	

	
resettextbox = Function({},
		sdb<<Set Text("01/01/2016");
		edb<<Set Text(char(Short Date(Today())));
		cb1b<<Set Text("###");
		cb2b<<Set Text("###");
	    cb3b<<Set Text("###");
		cb4b<<Set Text("###");	
		cb5b<<Set Text("###");
);


/**********************************************************************************************************************************************************************************************************************************************/

CB = New Window("Setup",
H List Box(
	V List Box(
		Text Box("Instructions:"),
		Text Box("FILL IN INSTRUCTIONS HERE")
	),

	V List Box(
		Text Box("Pick Date Range"),
		//Text Box("Start date must be after April 1, 2015."),
			Line Up Box(NCol(2),
			Text Box("Start Date: "),
			sdb = Text Edit Box("01/01/2016"),
			Text Box("End Date: "),
			edb = Text Edit Box(char(Short Date(Today())));
			)
		),
	
	V List Box(
		Text Box("Pick NC Code(s)"),
		Text Box("Code must be three digits"),
		Line Up Box(NCol(2),
		cb1a = Check Box(0),
		cb1b = Text Edit Box("###"),
		cb2a = Check Box(0),
		cb2b = Text Edit Box("###"),
		cb3a = Check Box(0),
		cb3b = Text Edit Box("###"),
		cb4a = Check Box(0),
		cb4b = Text Edit Box("###"),
		cb5a = Check Box(0),
		cb5b = Text Edit Box("###"),
		)
	),

	V List Box(
		Text Box("Pick Disposition(s)"),
		db1 = Check Box("Scrap Replace",0),
		db2 = Check Box("Scrap Do Not Replace", 0),
		db3 = Check Box( "Deviate - Material", 0),
		db4 = Check Box("Deviate - Process", 0),
		db5 = Check Box("Deviate - Product", 0),
		db6 = Check Box("Rework", 0)
	),
	
	V List Box(
		Text Box("Pick Display Range(s)"),
		rb1 = Check Box("Month",0),
		rb2 = Check Box("Quarter",0),
		rb3 = Check Box("Year",0)
	),
	V List Box(
		Button Box("OK",
	    getdata;
	    openncfiles;
	    cut2017;
	    numforcost;
	    lookup;
	    combinetables;
	    datelimit;
	    addcols;
	    costcols;
		),
		Button Box("Reset",
		Close All(Data Tables, No Save);
		resetcheckbox;
		resettextbox;
		),
		Button Box("Close",
		Close All(Data Tables, No Save);
		Close All(Reports, No Save);
		CB<<Close Window;
		)
	)
);	
);

7 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

I found a potential trouble spot in your code:

	CCT<<Select Where(:DISP.TYPES == "NOTDEFINED" | :DISP.TYPES == "DEVIATE" | :DISP.TYPES == "REVIEWED & REVERSED" | :DISP.TYPES == "SCRAP");
	CCT<<Delete Rows;
	CCT<<Clear Select;

If your select statement doesn't find anything you'll get an error message in the log about nothing being selected  Safer to do this:

del_rows = CCT << get rows Where(:DISP.TYPES == "NOTDEFINED" | :DISP.TYPES == "DEVIATE" | :DISP.TYPES == "REVIEWED & REVERSED" | :DISP.TYPES == "SCRAP");
if (nrows(del_rows) > 0,
	cct << delete rows(del_rows);
);

 

 

 

 

rfeick

Community Trekker

Joined:

Sep 30, 2016

That makes sense. Thanks for pointing that out. I've updated that section of the script, but am still struggling with the original issue of referencing the lists.

pmroz

Super User

Joined:

Jun 23, 2011

Maybe the use of evallist() is needed here.  Try running this code:

 

code1a = "aaa";
code2a = "bbb";
code3a = "ccc";
code4a = "ddd";
code5a = "eee";

codechecks1 = {code1a, code2a, code3a, code4a, code5a};

codechecks2 = evallist({code1a, code2a, code3a, code4a, code5a});

print(codechecks1);
print(codechecks2);

As you can see, the evallist() converts the variable names to their actual values, which is what I think you want.

rfeick

Community Trekker

Joined:

Sep 30, 2016

Adding in the Eval List function fixed the problem! Thanks!

Justin_Chilton

Joined:

Aug 27, 2015

I was going to suggest that you use EvalList as well. When you reference "dispchecks[d]" it only evaluates to the variable in that position, which can never be equal to 1. Eval List will make the actual values appear in the list.

 

Also, I have a tip for future long If statements. You can put many conditions within the same If statement, greatly reducing the complexity when you have a large number of conditions. The excerpt below is equivalent to the group of If statements in your formula column.

If(
	Month( :DETECT.DATE ) == 1, "January",
	Month( :DETECT.DATE ) == 2, "February",
	Month( :DETECT.DATE ) == 3, "March",
	Month( :DETECT.DATE ) == 4, "April",
	Month( :DETECT.DATE ) == 5, "May",
	Month( :DETECT.DATE ) == 6, "June",
	Month( :DETECT.DATE ) == 7, "July",
	Month( :DETECT.DATE ) == 8, "August",
	Month( :DETECT.DATE ) == 9, "September",
	Month( :DETECT.DATE ) == 10, "October",
	Month( :DETECT.DATE ) == 11, "November",
	Month( :DETECT.DATE ) == 12, "December",
	"You did something wrong"
);
Justin
rfeick

Community Trekker

Joined:

Sep 30, 2016

That makes things much simpler! I'll start using that format from now on.

rfeick

Community Trekker

Joined:

Sep 30, 2016

Moving forward, now that I've got all the columns for the entered codes showing up correctly is to create summary tables per NC code over different time ranges (month, year, etc.). I've done summary tables before, but never wiht a variable number of columns since the user can enter up to 5 NC codes. Is there a way to do this?