82129_en-US

liliconcom

Join Tables with Relative Reference

Hi;

 

I'm trying to join two tables, however, I want the reference of the column, and not the column name.  I have tried replacing the :Name("...") with Column(5), for example, and it doesn't not work.  I have also tried assigning Col5 = Column(5) and then using Col5 in the script but that doesn't work, either.

 

I've tried searching, and I can't seem to find anything that has helped.  Thanks!

 

Data Table( "IO Data Table" ) << Join(
 With( Data Table( "EA$" ) ),
 Select(
  :Plant,
  :Material,
  :INV,
  :Past Due,
  :Name( "Sum(QTY, 10/31/2018)" ),
  :Name( "Sum(QTY, 11/01/2018)" ),
  :Name( "Sum(QTY, 11/02/2018)" ),
  :Name( "Sum(QTY, 11/05/2018)" ),
  :Name( "Sum(QTY, 11/06/2018)" ),
  :Name( "Sum(QTY, 11/07/2018)" )
 ),
 SelectWith( :EA$ ),
 By Matching Columns( :Plant = :Plant, :Material = :Finished Good ),
 Drop multiples( 0, 0 ),
 Include Nonmatches( 0, 0 ),
 Preserve main table order( 1 ),
 Output Table( "Backlog Pivot" )
)

Re: Join Tables with Relative Reference

Thanks for the response, Jim.  When I was reading through your example I realized I had forgotten something in my code, and it works now.

 

Col5 = Column( CONCTable, 5 );
Col6 = Column( CONCTable, 6 );
Col7 = Column( CONCTable, 7 );
Col8 = Column( CONCTable, 8 );
Col9 = Column( CONCTable, 9 );
Col10 = Column( CONCTable, 10 );

IO2 = Data Table( "IO Data Table" ) << Join(
 With( Data Table( "EA$" ) ),
 Select(
  :Plant,
  :Material,
  :INV,
  :Past Due,
  Col5,
  Col6,
  Col7,
  Col8,
  Col9,
  Col10
 ),
 SelectWith( :EA$ ),
 By Matching Columns( :Plant = :Plant, :Material = :Finished Good ),
 Drop multiples( 0, 0 ),
 Include Nonmatches( 0, 0 ),
 Preserve main table order( 1 ),
 Output Table( "Backlog Pivot" )
);

Re: Join Tables with Relative Reference

If I am understanding your question, below is a script that uses a couple of different types of relative referencing to produce a joined table.  

Names Default To Here( 1 );
dt1 = Open( "$SAMPLE_DATA\big class.jmp" );
dt2 = Open( "$SAMPLE_DATA\big class families.jmp" );

namecol = Column( dt1, "Name" );
i = 3;

Data Table( "Big Class" ) << Join(
 With( Data Table( "big class families" ) ),
 Merge Same Name Columns,
 Match Flag( 0 ),
 Select( :sex, :height, :weight ),
 SelectWith( :sibling ages ),
 By Matching Columns( namecol = :name, :age = Column( dt2, i ) ),
 Drop multiples( 0, 0 ),
 Include Nonmatches( 0, 0 ),
 Preserve main table order( 1 )
);