Subscribe Bookmark RSS Feed

Using column data to script table edits

fwissman

Community Trekker

Joined:

May 21, 2015

Hi - I apologize if this is trivial.

I have created a table SublotList from Sublot Control Data that is a single column of Sublots, they are mixed alphanumeric and not known until the original table is pulled.  I would like to use the Sublot found in the 1st row of SublotList and use it in my Select Where statement and also in the Output Table name. Then I want to step through all Sublots and quit when I use the last Sublot.  Roughly said, I'm manually entering "-A",   -A, "-L22", or -L22, etc. in many select and naming statements and would like to use these values from a separate table. I'm sure this question isn't fully formed yet, please ask questions. Thank you.

 

dt1<< Select Where (:SubLot == "-A");

Data Table( "Sublot Control Data" ) <<

Subset(

Output Table( "-A Sublot Control Data" ),

Selected Rows( 1 ), ,

Selected columns only( 0 ));

 

1 ACCEPTED SOLUTION

Accepted Solutions
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Solution

@fwissman , 

  Here I think this is what you are after . Let me know : 

// Define Paths (edit this as needed) 
SubLotControlFilePath = "C:\Users\Sublot Control Data.jmp" 
SublotListFilePath = "C:\Users\Sublot List.jmp" ;

// Open Files 
dt_SublotControl = Open(SubLotControlFilePath);
dt_SublotList = Open(SublotListFilePath);

// Get List of sublots
SubLotList = dt_SublotList:SubLot << Get Values; 

// If you want to select all rows where sublots match sublot list 
dt_SublotControl << Select Where(Contains(SubLotList,:SubLot)); 

// If you instead want to select rows based on subplot one after the other 
for( i = 1 , i <= N Items(SubLotList) , i++, 
		dt_SublotControl << Select Where(:SubLot == SubLotList[i]); 
		dt_Temp = dt_SublotControl << Subset(""); 
		dt_Temp << set Name(Concat("Subplot-",SubLotList[i]));
   );
5 REPLIES
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Hello @fwissman : 
     Can you upload a subset of the data table you are using ? I think the question can be addressed . 

  

     If I understand your question correctly , you essentially want to select rows in a data table but the selection criteria is based on values from a column in the table . 

     If this is correct - see if the following helps , else upload the sample data and I will try and answer your question . 

     I am using the "Airline Delays" data table from the Sample Data you can find under "Help" 

   

 // Select the data table you want to work with 
dt = Current Data Table(); 

// Get as list the values in the column 
DaysOfMonth = As List(dt:Day of Month << get Values()); 

// Specify the sublist in your case sublots that you want to select 
DesiredDays = {1,5,7}; 

// Select the rows that match the criteria by wrapping in Contains 
dt << Select Where(Contains(DesiredDays,:Day of Month));

    

 

 

 

Best 

Uday 

fwissman

Community Trekker

Joined:

May 21, 2015

Hi, Uday, Here is my original table, Sublot Control Data, and my derived table, Sublot List. Please note I want to use all of the entries in Sublot List.
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Solution

@fwissman , 

  Here I think this is what you are after . Let me know : 

// Define Paths (edit this as needed) 
SubLotControlFilePath = "C:\Users\Sublot Control Data.jmp" 
SublotListFilePath = "C:\Users\Sublot List.jmp" ;

// Open Files 
dt_SublotControl = Open(SubLotControlFilePath);
dt_SublotList = Open(SublotListFilePath);

// Get List of sublots
SubLotList = dt_SublotList:SubLot << Get Values; 

// If you want to select all rows where sublots match sublot list 
dt_SublotControl << Select Where(Contains(SubLotList,:SubLot)); 

// If you instead want to select rows based on subplot one after the other 
for( i = 1 , i <= N Items(SubLotList) , i++, 
		dt_SublotControl << Select Where(:SubLot == SubLotList[i]); 
		dt_Temp = dt_SublotControl << Subset(""); 
		dt_Temp << set Name(Concat("Subplot-",SubLotList[i]));
   );
fwissman

Community Trekker

Joined:

May 21, 2015

Hi, Uday, Fantastic - that is exactly what I needed! Cheers! Frank
txnelson

Super User

Joined:

Jun 22, 2012

Here is one method that uses Joining and Subsetting with a By clause that does what is wanted

Names Default to Here(1);
dtMain=data table("Sublot Control Data");
dtList=data table("Sublot List");

dtTogether = dtMain << Join(
	With( dtList ),
	Merge Same Name Columns,
	By Matching Columns( :SubLot = :SubLot ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 )
);

dtTogether << delete columns("Match Flag");

dtFinal = dtTogether << Subset(
	By( :SubLot ),
	selected columns( 0 ),
	selected rows( 0 )
);
Jim