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

jenkins_macedo

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

 

jvillaumie

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