Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
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.