Ich versuche, den Benutzer aufzufordern, Dateien auszuwählen, deren Name ein Jahr endet. Beim Durchlaufen der ersten Datei (2014) möchte ich, dass der Benutzer auswählt, welche Produkte herausgefiltert werden sollen, und entsprechend eine Tabelle dieser Ausgabe erstellt. Ich möchte dann, falls zutreffend, dass das Skript zum nächsten Jahr (2015) wechselt, um dann AUCH ein Fenster zu generieren, in dem der Benutzer auswählen kann, welche Produkte aus diesem Jahr herausgefiltert und erneut eine Tabellenausgabe generiert werden sollen. Ich möchte dies letztendlich für eine beliebige Anzahl von Jahren tun (zB 2016,2017, 2018, 2019) ... aber aus irgendeinem Grund überspringt es den Filter "2014" und gibt mir nur eine "2015" -Aufforderung:
//*** 1. Open GCH data from NAR and save them as JMP data tables **********
Names Default To Here( 1 );
Close All( data tables );
Delete Symbols();
Directory = Pick Directory( "Select a directory" );
Files = Pick File( "Select Data Set(s)", Directory, {"All Files|*", "JMP Files|jmp;jsl;jrn", "Excel Files|xlsx"}, 1, 0, "", "multiple" );
spreadsheetList = {"Product Events", "Products", "CodesFrmPLI"};
For( i = 1, i <= N Items( Files ), i++,
// For this file, detremine the year
If(
Contains( Files[i], "2014" ), theYear = "2014",
Contains( Files[i], "2015" ), theYear = "2015"
);
// Read in separately each separte spreadsheet
For( k = 1, k <= N Items( spreadsheetList ), k++,
//Create a list that will contain the current spreadsheet to be read in
extractList = {};
Insert Into( extractList, spreadsheetList[k] );
Try(
dt = Open(
Files[i],
Worksheets( NWorksheet = extractList ),
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( 0 ),
Replicated Spanned Rows( 1 ),
Replicated Spanned Headers( 0 ),
Suppress Hidden Rows( 1 ),
Suppress Hidden Columns( 1 ),
Suppress Empty Columns( 1 ),
Treat as Hierarchy( 1 ),
Multiple Series Stack( 0 ),
Import Cell Colors( 0 ),
Limit Column Detect( 0 ),
Column Separator String( "-" ),
invisible
)
)
);
// The name for the JMP file is the same as the spreadsheet name, except for the Codes,
If( extractList[1] == "CodesFrmPLI",
extractList[1] = "Codes"
);
// Save the JMP file with the prescribed name
dt << Save( extractList[1] || "_" || theYear || ".jmp" );
);
);
Show( Files );
//*********************** Join Product Events with Products for 2014 *******************************\\
If( Contains( Files, "2014" ),
theYear = "2014"
);
Show( theYear );
If( theYear == "2014",
Show( "It's 2014*********************************!" );
Try(
//*** Create a 2014 Folder in the base directory****
baseDir = "$DOCUMENTS";
dir = baseDir || "/2014";
Create Directory( dir );
dt2 = Data Table( "Product Events_2014" ) << Join(
With( Data Table( "Products_2014" ) ),
SelectWith( :PE PLI number ),
Select( :Notified Date, :Event Description ),
SelectWith( :Product Type, :Product CFN, :Product Family ),
By Matching Columns( :Product Event ID = :Product Event ID ),
Drop multiples( 0, 0 ),
Include Nonmatches( 0, 0 ),
Preserve main table order( 1 )
);
dt2 << Save( dir || "\Product Events_Products_2014.jmp" );
dt1 = Data Table( "Product Events_Products_2014" ) << Join(
With( Data Table( "Codes_2014" ) ),
Select( :PE PLI number, :Notified Date, :Event Description, :Product Type, :Product CFN, :Product Family ),
SelectWith( :CodeTypePrmaryAndDesc, :Code Type, :Code LLT, :Code LLT Desc ),
By Matching Columns( :PE PLI number = :PE PLI number ),
Drop multiples( 0, 0 ),
Include Nonmatches( 0, 0 ),
Preserve main table order( 1 )
);
dt1 << Save( dir || "\Product_events_Codes_2014.jmp" );
//*** Filter Out Primary RFR Codes
dt1 << select where( :Code Type == "RFR" );
Wait( 0 );
df = dt1 << subset( selected rows( 1 ), selected columns( 0 ) );
df << Save( dir || "\RFR_2014.jmp" );
Wait( 0 );
dt1 << select where( :Code Type == "OUT" );
Wait( 0 );
df = dt1 << subset( selected rows( 1 ), selected columns( 0 ) );
df << Delete Columns(
{"CodeTypePrmaryAndDesc", "CodeTypePrmaryAndDesc 1", "CodeTypePrmaryAndDesc 2", "CodeTypePrmaryAndDesc 3", "CodeTypePrmaryAndDesc 4"}
);
df << Save( dir || "\OUT_2014.jmp" );
Wait( 0 );
dt1 << Text To Columns( delimiter( ";" ), columns( :CodeTypePrmaryAndDesc ) );
obj = dt1 << Select Where( Contains( "RFR", :CodeTypePrmaryAndDesc 1 ) & Contains( "Y", :CodeTypePrmaryAndDesc 2 ) );
dt = dt1 << subset( selected rows( 1 ), selected columns( 0 ) );
dt << Delete Columns(
{"CodeTypePrmaryAndDesc", "CodeTypePrmaryAndDesc 1", "CodeTypePrmaryAndDesc 2", "CodeTypePrmaryAndDesc 3", "CodeTypePrmaryAndDesc 4"}
);
dt << Save( dir || "\PrimaryRFR_2014.jmp" );
dt << clear select;
dt2 = Data Table( "PrimaryRFR_2014" ) << Join(
With( Data Table( "OUT_2014" ) ),
Select(
:PE PLI number,
:Notified Date,
:Event Description,
:Product Type,
:Product CFN,
:Product Family,
:Code Type,
:Code LLT,
:Code LLT Desc
),
SelectWith( :Code Type, :Code LLT, :Code LLT Desc ),
By Matching Columns( :PE PLI number = :PE PLI number ),
Drop multiples( 0, 0 ),
Include Nonmatches( 0, 0 ),
Preserve main table order( 1 )
);
dt2 << Save( dir || "\PrimaryRFR_OUT_2014.jmp" );
tab = dt2 << Tabulate(
Add Table(
Column Table( Grouping Columns( :Code LLT of OUT_2014 ) ),
Row Table( Grouping Columns( :Code LLT of PrimaryRFR_2014, :Code LLT Desc of PrimaryRFR_2014 ) )
)
);
dt_tab = tab << Make Into Data Table;
dt_tab << set name( "Tabulate" );
dt_tab << Save( dir || "\PrimaryRFR_OUT_Tabulate_2014.jmp" );
//*** Create a window to prompt user to filter for Model numbers they would like to use
prods = Associative Array( Column( dt2, "Product CFN" ) << getValues ) << getKeys;
OKscript2014 = Expr(
SelectedProds = lb1 << getSelected;
selectedRows = [];
For( p = 1, p <= N Items( SelectedProds ), p++,
selectedRows = V Concat( selectedRows, dt2 << getRowsWhere( :Product CFN == SelectedProds[p] ) )
);
dt5 = dt2 << Subset( Rows( selectedRows ), LinkToOriginalDataTable( 1 ) );
Wait( 0 );
dt5 << Save( dir || "\PrimaryRFR_ProductFiltered_2014.jmp" );
);
nw1 = New Window( "2014 Product Filter", <<MODAL></MODAL>Mir muss hier etwas Einfaches fehlen ... Kann jemand bitte versuchen zu helfen, was los sein könnte?
Vielen Dank,
Zach
