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;
)
)
);
);
