BookmarkSubscribe
Choose Language Hide Translation Bar
Highlighted
zjuv007
Contributor

Multiple filter boxes not working

I am trying to ask the user to select files that have a year ending in their name. As I go through the first file (2014), I want the user to select which products to filter out and correspondingly generate a table of this output. I then want, if applicable, for the script to go to the next year (2015) to then ALSO generate a window to have the user select which products from this year to filter out and again generate a table output. I would ultimately like to do this for an arbitrary number of years (e.g. 2016,2017, 2018, 2019)... but for some reason it skips the "2014" filter and just gives me a "2015" prompt:

 

//*** 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, lb1 = List Box( prods ), Button Box( "OK", OKscript2014 ) );
		Print( "IT WAITED 2014!!!!!!****************" );
	);
);
	
	
//*********************** Join Product Events with Products for 2015 *******************************\\

If( Contains( Files, "2015" ),
	theYear = "2015"
);
If( theYear == "2015",
	Show( "It's 2015*********************************!" );

	Try(
//*** Create a 2015 Folder in the base directory****
		baseDir = "$DOCUMENTS";
		dir = baseDir || "/2015";
		Create Directory( dir );
		
		dt2 = Data Table( "Product Events_2015" ) << Join(
			With( Data Table( "Products_2015" ) ),
			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_2015.jmp" );
	
		dt1 = Data Table( "Product Events_Products_2015" ) << Join(
			With( Data Table( "Codes_2015" ) ),
			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_2015.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_2015.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_2015.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_2015.jmp" );
		dt << clear select;
	
		dt20 = Data Table( "PrimaryRFR_2015" ) << Join(
			With( Data Table( "OUT_2015" ) ),
			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 )
		);
		dt20 << Save( dir || "\PrimaryRFR_OUT_2015.jmp" );

		tab = dt20 << Tabulate(
			Add Table(
				Column Table( Grouping Columns( :Code LLT of OUT_2015 ) ),
				Row Table( Grouping Columns( :Code LLT of PrimaryRFR_2015, :Code LLT Desc of PrimaryRFR_2015 ) )
			)
		);
		dt_tab = tab << Make Into Data Table;
		dt_tab << set name( "Tabulate2" );
		dt_tab << Save( dir || "\PrimaryRFR_OUT_Tabulate_2015.jmp" );
	
//*** Create a window to prompt user to filter for Model numbers they would like to use
		prods = Associative Array( Column( dt20, "Product CFN" ) << getValues ) << getKeys;
		OKscript2015 = Expr(
		SelectedProds = lb1 << getSelected;
		selectedRows = [];
		For( p = 1, p <= N Items( SelectedProds ), p++,
		selectedRows = V Concat( selectedRows, dt20 << getRowsWhere( :Product CFN == SelectedProds[p] ) )
			);
	dt6 = dt20 << Subset( Rows( selectedRows ), LinkToOriginalDataTable( 0 ) );
	Wait( 0 );
	dt6 << Save( dir || "\PrimaryRFR_ProductFiltered_2015.jmp" );
);
nw1 = New Window( "2015 Product Filter", <<Modal, lb1 = List Box( prods ), Button Box( "OK", OKscript2015 ) );
		Print( "IT WAITED 2015!!!!!!****************" );
		);

	);

I've got to be missing something simple here... Can anyone please try to assist in what might be the matter?

 

Thank you,

Zach

0 Kudos