Joining tablesI 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@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).
Re: Joining tablesI 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@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 tablesYou can use the Search(Find) dialog to change column names, or parts of column names
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" )
);
Re: Joining tablesHi Uday, I have added the tables.
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";
Re: Joining tables How do you recode column names? @pmroz Also, the Recode option is greyed out for my table.
Re: Joining tablesUday, 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 tablesCols > Utilities > Recode will allow you to change values to standard values.
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
Re: Joining tablesHow can I manipulate the names in column to look the same as the table that it needs to join to?
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.
