Sto cercando di chiedere all'utente di selezionare i file che hanno un anno che termina con il loro nome. Mentre passo al primo file (2014), voglio che l'utente selezioni i prodotti da filtrare e generino corrispondentemente una tabella di questo output. Quindi, se possibile, desidero che lo script vada al prossimo anno (2015) per poi generare ANSO una finestra in modo che l'utente possa selezionare i prodotti di quest'anno da filtrare e generare nuovamente un output di tabella. In ultima analisi, mi piacerebbe farlo per un numero arbitrario di anni (ad esempio, 2016,2017, 2018, 2019) ... ma per qualche motivo salta il filtro "2014" e mi dà solo un suggerimento "2015":
//*** 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>Devo perdermi qualcosa di semplice qui ... Qualcuno può per favore cercare di aiutare in quello che potrebbe essere il problema?
Grazie,
Zach
