Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Joining tables

News

We’re asking you to select a content label when starting a new topic in the Discussions area. Read more to find out why.

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 15, 2018 9:10 AM
(3704 views)

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.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Best

Uday

Uday

13 REPLIES 13

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Uday

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Joining tables

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Uday

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Uday

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Joining tables

Hi Uday, I have added the tables.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Best

Uday

Uday

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Joining tables

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Joining tables

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