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

How do I merge/join multiple JMP data tables with the same variables within a script?

I know there is a way to this, but maybe someone may have an answer somewhere here. Is there way to join a multiple data tables with similar numeric and categorical data by fiscal year with a single script?

 

Example is a script I am trying to use.

 

 

Data Table( "confirmed_enrollment_FY2006" ) << Join(
  With( Data Table( "confirmed_enrollment_FY2007", "confirmed_enrollment_FY2008", "confirmed_enrollment_FY2009" ) ),
  Merge Same Name Columns,
  Select(
  :Fiscal Year,
  :Mail Code,
  :ID,
  :HH Key,
  :Program #,
  :Order Number,
  :Order Date,
  :Name( "# Confirmed Enrollments" )
  ),
  SelectWith(
  :Fiscal Year,
  :Mail Code,
  :ID,
  :HH Key,
  :Program #,
  :Order Number,
  :Order Date,
  :Name( "# Confirmed Enrollments" )
  ),
  By Matching Columns(
  :Mail Code = :Mail Code,
  :ID = :ID,
  :HH Key = :HH Key,
  :Program # = :Program #,
  :Order Number = :Order Number
  ),
  Drop multiples( 0, 0 ),
  Include Nonmatches( 1, 1 ),
  Preserve main table order( 1 )
)

 

Jenkins Macedo
1 ACCEPTED SOLUTION

Accepted Solutions
jvillaumie
Level III

Re: Script to merge/join multiple data tables with the same variables

You could try joining the first 2 tables and using the result as the argument for another join with the 3rd table, and so on (see script below). I am not sure what you are trying to do so I may be misunderstanding what you are trying to achieve, but you might also consider staking all your tables together, rather than joining, and tick "create source table" so you keep track of which year each person enrolled; columns with the same name from the different tables will automatically be lined up.

 

((Data Table( "confirmed_enrollment_FY2006" ) << Join(
With( Data Table( "confirmed_enrollment_FY2007") ),
Merge Same Name Columns,
Select(
:Fiscal Year,
:Mail Code,
:ID,
:HH Key,
:Program #,
:Order Number,
:Order Date,
:Name( "# Confirmed Enrollments" )
),
SelectWith(
:Fiscal Year,
:Mail Code,
:ID,
:HH Key,
:Program #,
:Order Number,
:Order Date,
:Name( "# Confirmed Enrollments" )
),
By Matching Columns(
:Mail Code = :Mail Code,
:ID = :ID,
:HH Key = :HH Key,
:Program # = :Program #,
:Order Number = :Order Number
),
Drop multiples( 0, 0 ),
Include Nonmatches( 1, 1 ),
Preserve main table order( 1 )
)) << Join(
With( Data Table( "confirmed_enrollment_FY2008") ),
Merge Same Name Columns,
Select(
:Fiscal Year,
:Mail Code,
:ID,
:HH Key,
:Program #,
:Order Number,
:Order Date,
:Name( "# Confirmed Enrollments" )
),
SelectWith(
:Fiscal Year,
:Mail Code,
:ID,
:HH Key,
:Program #,
:Order Number,
:Order Date,
:Name( "# Confirmed Enrollments" )
),
By Matching Columns(
:Mail Code = :Mail Code,
:ID = :ID,
:HH Key = :HH Key,
:Program # = :Program #,
:Order Number = :Order Number
),
Drop multiples( 0, 0 ),
Include Nonmatches( 1, 1 ),
Preserve main table order( 1 )
)) << Join(
With( Data Table( "confirmed_enrollment_FY2009") ),
Merge Same Name Columns,
Select(
:Fiscal Year,
:Mail Code,
:ID,
:HH Key,
:Program #,
:Order Number,
:Order Date,
:Name( "# Confirmed Enrollments" )
),
SelectWith(
:Fiscal Year,
:Mail Code,
:ID,
:HH Key,
:Program #,
:Order Number,
:Order Date,
:Name( "# Confirmed Enrollments" )
),
By Matching Columns(
:Mail Code = :Mail Code,
:ID = :ID,
:HH Key = :HH Key,
:Program # = :Program #,
:Order Number = :Order Number
),
Drop multiples( 0, 0 ),
Include Nonmatches( 1, 1 ),
Preserve main table order( 1 )
));

View solution in original post

1 REPLY 1
jvillaumie
Level III

Re: Script to merge/join multiple data tables with the same variables

You could try joining the first 2 tables and using the result as the argument for another join with the 3rd table, and so on (see script below). I am not sure what you are trying to do so I may be misunderstanding what you are trying to achieve, but you might also consider staking all your tables together, rather than joining, and tick "create source table" so you keep track of which year each person enrolled; columns with the same name from the different tables will automatically be lined up.

 

((Data Table( "confirmed_enrollment_FY2006" ) << Join(
With( Data Table( "confirmed_enrollment_FY2007") ),
Merge Same Name Columns,
Select(
:Fiscal Year,
:Mail Code,
:ID,
:HH Key,
:Program #,
:Order Number,
:Order Date,
:Name( "# Confirmed Enrollments" )
),
SelectWith(
:Fiscal Year,
:Mail Code,
:ID,
:HH Key,
:Program #,
:Order Number,
:Order Date,
:Name( "# Confirmed Enrollments" )
),
By Matching Columns(
:Mail Code = :Mail Code,
:ID = :ID,
:HH Key = :HH Key,
:Program # = :Program #,
:Order Number = :Order Number
),
Drop multiples( 0, 0 ),
Include Nonmatches( 1, 1 ),
Preserve main table order( 1 )
)) << Join(
With( Data Table( "confirmed_enrollment_FY2008") ),
Merge Same Name Columns,
Select(
:Fiscal Year,
:Mail Code,
:ID,
:HH Key,
:Program #,
:Order Number,
:Order Date,
:Name( "# Confirmed Enrollments" )
),
SelectWith(
:Fiscal Year,
:Mail Code,
:ID,
:HH Key,
:Program #,
:Order Number,
:Order Date,
:Name( "# Confirmed Enrollments" )
),
By Matching Columns(
:Mail Code = :Mail Code,
:ID = :ID,
:HH Key = :HH Key,
:Program # = :Program #,
:Order Number = :Order Number
),
Drop multiples( 0, 0 ),
Include Nonmatches( 1, 1 ),
Preserve main table order( 1 )
)) << Join(
With( Data Table( "confirmed_enrollment_FY2009") ),
Merge Same Name Columns,
Select(
:Fiscal Year,
:Mail Code,
:ID,
:HH Key,
:Program #,
:Order Number,
:Order Date,
:Name( "# Confirmed Enrollments" )
),
SelectWith(
:Fiscal Year,
:Mail Code,
:ID,
:HH Key,
:Program #,
:Order Number,
:Order Date,
:Name( "# Confirmed Enrollments" )
),
By Matching Columns(
:Mail Code = :Mail Code,
:ID = :ID,
:HH Key = :HH Key,
:Program # = :Program #,
:Order Number = :Order Number
),
Drop multiples( 0, 0 ),
Include Nonmatches( 1, 1 ),
Preserve main table order( 1 )
));