Choose Language Hide Translation Bar
Highlighted
PNash
Level II

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" )
)
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
PNash
Level II

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

View solution in original post

2 REPLIES 2
Highlighted
txnelson
Super User

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 )
);
Jim
Highlighted
PNash
Level II

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

View solution in original post

Article Labels

    There are no labels assigned to this post.