Choose Language Hide Translation Bar
Highlighted
zjuv007
Level III

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

Article Labels

    There are no labels assigned to this post.