나는 그들의 이름으로 1 년이 끝나는 파일을 선택하라고 사용자에게 요청하려고합니다. 첫 번째 파일 (2014)을 살펴볼 때 사용자가 필터링 할 제품을 선택하고 그에 따라이 출력 테이블을 생성하길 원합니다. 그런 다음 해당 스크립트가 내년 (2015)으로 이동하기를 원한다면 올해 사용자가 어떤 제품을 걸러 내고 다시 테이블 출력을 생성할지 선택할 수있는 창을 생성하고 싶습니다. 나는 궁극적으로 임의의 해 (예 : 2016,2017, 2018, 2019) 동안 이것을하고 싶다. 어떤 이유로 2014 필터를 건너 뛰고 "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>나는 여기서 간단한 것을 놓치고있어. 누구든지 문제가 될 수 있도록 도와 주시겠습니까?
고맙습니다,
자크
