cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Aam_jmp
Level IV

Joining tables

I have to join two tables  using jsl. The first table looks like this:

Example 1.PNG 

However, the second table can either be like this:

 

 Example2.PNG

In this table, Pred Form Molecule 1 (%)(2) is the same as Molecule 1(%) and the numbers in the value column are repeated. How can I specify the join to only consider one of those values?

 

The second table to join can look like this:

Example3.PNG

 

In this case, how can I specify in the join that Molecule 1 (%) in the first table is the same as Pred Form Molecule1 (%)(2) in the table that needs to be joined. 

The final table that I need should be something like this:

Example4.PNG 

Thank you for any help.

1 ACCEPTED SOLUTION

Accepted Solutions
uday_guntupalli
Level VIII

Re: Joining tables

@Aam_jmp


I have tested this code using tables try and join. You can expand it appropriately to include try1 if needed. 

 

 

Clear Log(); Clear Globals(); 

// Open Data Tables 
dt1 = Open(FilePath1); // Where FilePath1 points to your file location for JOIN table 
dt2 = Open(FilePath2); // Where FilePath2 points to your file location for try table 

// Get Y values from main table 
YVals = dt1:Y << Get Values; 

// Get Col values from second table 
ColVals = dt2:Col << Get Values; 

// Loop through the values 
for(i = 1, i <= N Items(YVals), i++,
		for(r = 1, r <= N Items(ColVals), r++,
				If(Contains(ColVals[r],YVals[i]),
					ColVals[r] = YVals[i];
				  );
		   );	
   );

// Assign Corrected Values back to dt2 
dt2 << New Column("CorrectedCol",Character,Continuous, << Set Values(ColVals)); 

// Join Tables 
dt_Intermediate = dt1 << Join(With(dt2 ),
								Select( :Y, :Quan Reqd ),
								SelectWith( :Value ),
								By Matching Columns( :Y = :CorrectedCol ),
								Drop multiples( 0, 0 ),
								Include Nonmatches( 0, 0 ),
								Preserve main table order( 1 )
							);
							
// Remove Duplicates 
dt_Results = dt_Intermediate << Summary(
											Group( :Y, :Quan Reqd, :Value ),
											Freq( "None" ),
											Weight( "None" )
									   );

image.png

 

Best
Uday

View solution in original post

13 REPLIES 13
uday_guntupalli
Level VIII

Re: Joining tables

@Aam_jmp
           Inner Join between tables will look for the columns to match . Can you rename rows that are the same in table 2 ? That will automatically give the result that you want. Unless your rows are too many where you can't keep track, this is one way to do it. 

Best
Uday
Aam_jmp
Level IV

Re: Joining tables

How can I manipulate the names in column to look the same as the table that it needs to join to?

uday_guntupalli
Level VIII

Re: Joining tables

@Aam_jmp
   2 steps.

Step 1: Extract the values of titles in table 1

Step 2: Loop through the titles in table 1 and look for similar names in table 2, then update the row to reflect the same

Best
Uday
Aam_jmp
Level IV

Re: Joining tables

Uday, I am not even able to extract the column names I guess because of the numbers and "%" sign in them. Can you direct me as to how I can do it? For now, I am working on this script to extract the names. Thanks.

 

dt = Open( "C:\Users\filepath\try1.jmp" );

col_names = dt << Get Column Names();

Show(col_names);

uday_guntupalli
Level VIII

Re: Joining tables

@Aam_jmp
         See below some sample code. This should work for any data table in JMP. If it is not, I would request you share a sample of your data tables and I can try to see what the issue is. 

 

Clear Log(); Clear Globals(); 

// Open Sample Data 
dt = Open( "$SAMPLE_DATA/Cities.jmp" );

// Extract Column Names
ColNames = dt << Get Column Names("String"); 

// Extract Rows from a column 
Cities = dt:city << Get Values; 

// Change Column Name 
Col = Column(dt,1); 
Col << Set Name("City_Edit");

// Change Row Values 
dt:City_Edit[7] = "TEMPE"; 


Best
Uday
Aam_jmp
Level IV

Re: Joining tables

Hi Uday, I have added the tables. 

uday_guntupalli
Level VIII

Re: Joining tables

@Aam_jmp


I have tested this code using tables try and join. You can expand it appropriately to include try1 if needed. 

 

 

Clear Log(); Clear Globals(); 

// Open Data Tables 
dt1 = Open(FilePath1); // Where FilePath1 points to your file location for JOIN table 
dt2 = Open(FilePath2); // Where FilePath2 points to your file location for try table 

// Get Y values from main table 
YVals = dt1:Y << Get Values; 

// Get Col values from second table 
ColVals = dt2:Col << Get Values; 

// Loop through the values 
for(i = 1, i <= N Items(YVals), i++,
		for(r = 1, r <= N Items(ColVals), r++,
				If(Contains(ColVals[r],YVals[i]),
					ColVals[r] = YVals[i];
				  );
		   );	
   );

// Assign Corrected Values back to dt2 
dt2 << New Column("CorrectedCol",Character,Continuous, << Set Values(ColVals)); 

// Join Tables 
dt_Intermediate = dt1 << Join(With(dt2 ),
								Select( :Y, :Quan Reqd ),
								SelectWith( :Value ),
								By Matching Columns( :Y = :CorrectedCol ),
								Drop multiples( 0, 0 ),
								Include Nonmatches( 0, 0 ),
								Preserve main table order( 1 )
							);
							
// Remove Duplicates 
dt_Results = dt_Intermediate << Summary(
											Group( :Y, :Quan Reqd, :Value ),
											Freq( "None" ),
											Weight( "None" )
									   );

image.png

 

Best
Uday
pmroz
Super User

Re: Joining tables

Cols > Utilities > Recode will allow you to change values to standard values.

Aam_jmp
Level IV

Re: Joining tables

 How do you recode column names? @pmroz Also, the Recode option is greyed out for my table.