topic Joining tables in Discussions

Joining tables

Aam_jmp — Thu, 15 Mar 2018 23:37:45 GMT

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.

Re: Joining tables

uday_guntupalli — Thu, 15 Mar 2018 16:56:46 GMT

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

Re: Joining tables

Aam_jmp — Thu, 15 Mar 2018 17:52:21 GMT

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

Re: Joining tables

uday_guntupalli — Thu, 15 Mar 2018 18:43:46 GMT

@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

Re: Joining tables

pmroz — Thu, 15 Mar 2018 18:45:39 GMT

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

Re: Joining tables

Aam_jmp — Thu, 15 Mar 2018 21:18:17 GMT

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

Re: Joining tables

Aam_jmp — Thu, 15 Mar 2018 21:27:07 GMT

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

Re: Joining tables

uday_guntupalli — Thu, 15 Mar 2018 22:08:49 GMT

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

Re: Joining tables

Aam_jmp — Thu, 15 Mar 2018 23:38:50 GMT

Hi Uday, I have added the tables.

Re: Joining tables

uday_guntupalli — Fri, 16 Mar 2018 03:04:10 GMT

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

Re: Joining tables

txnelson — Fri, 16 Mar 2018 03:35:34 GMT

You can use the Search(Find) dialog to change column names, or parts of column names

Re: Joining tables

uday_guntupalli — Fri, 16 Mar 2018 13:25:37 GMT

@txnelson & @pmroz,
If I may, I would like to ask one question, why would you recommend column names be recoded / renamed given the table structure? The current structure of the table shared shows that the data is in the rows. While I understand, that one can always transpose the data and rename columns, wondering if that is the approach you are recommending ? Please advice

Re: Joining tables

pmroz — Fri, 16 Mar 2018 13:34:03 GMT

I was suggesting recode to change values in a column, not change the name of the column. To use the recode option, highlight the column you wish to recode, and click on Cols > Utilities > Recode (v12) or Cols > Recode (v13).

Re: Joining tables

uday_guntupalli — Fri, 16 Mar 2018 13:35:36 GMT

@pmroz,
Thank you for clarifying. My bad, was wondering if there was a better way to approach this that I was missing.

@pmrozwrote:
I was suggesting recode to change values in a column, not change the name of the column. To use the recode option, highlight the column you wish to recode, and click on Cols > Utilities > Recode (v12) or Cols > Recode (v13).