Choose Language Hide Translation Bar
Highlighted
zjuv007
Level III

How can I have two product filter boxes in one script and not have it get overwritten?

It appears that my "2015" data overruns my product filter when I run the "2014" OK Script ... is this a naming issue or am I missing something?

 

//*** 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" );
	);
);


//*********************** Join Product Events with Products for 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;
	
	dt 2 = 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;
	nw1 = New Window( "2014 Product Filter", lb1 = List Box( prods ), Button Box( "OK", OKscript ) );
	
	OKscript = Expr(
		nw1 << closeWindow;
		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( 0 ) );
		Wait( 0 );
			
		dt5 << Save( dir || "\PrimaryRFR_ProductFiltered_2014.jmp" );
	);

);
	
	
	
	
//*********************** Join Product Events with Products for 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;
	
	dt 2 = 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( "Tabulate" );
	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;
	nw2 = New Window( "2015 Product Filter", lb2 = List Box( prods ), Button Box( "OK", OKscript ) );
	
	OKscript = Expr(
		nw2 << closeWindow;
		SelectedProds = lb2 << getSelected;
		selectedRows = [];
		For( p = 1, p <= N Items( SelectedProds ), p++,
			selectedRows = V Concat( selectedRows, dt20 << getRowsWhere( :Product CFN == SelectedProds[p] ) )
		);
		dt5 = dt20 << Subset( Rows( selectedRows ), LinkToOriginalDataTable( 0 ) );
		Wait( 0 );
			
		dt5 << Save( dir || "\PrimaryRFR_ProductFiltered_2015.jmp" );
	);
		
);
Article Labels

    There are no labels assigned to this post.