Joining tables

[#1]

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

 

However, the second table can either be like this:

 

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:

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:

 

Thank you for any help.

[#2]

@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. 

[#3]

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

[#4]

@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

[#5]

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);

[#6]

@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"; 

[#7]

Hi Uday, I have added the tables. 

[#8 · Accepted solution]

@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" )
									   );

[#9]

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

[#10]

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