Subscribe Bookmark RSS Feed

How can I add colums into an output table?

carias0316

New Contributor

Joined:

Jul 16, 2016

Hi,

I joined two tables by matching two columns, this generates a new data table,  but now I have to add a new column with a formula into the output table generated after a join the previous ones, but for some reason I can't make it work. This it what I tried so far

Data Table( dt1 ) << Join(

With( Data Table( dt2 ) ),

By Matching Columns( :XNative = :XNative, :YNative = :YNative ),

Copy formula(0),

Suppress formula evaluation(0),

Drop multiples( 0, 0 ),

Include NonMatches(1, 1),

Preserve main table order( 1 ),

Output Table Name( "Contour Plot")));

Wait(2);

data table("Contour Plot") << New Column("subs", Numeric, Continuous, Format("Best", 8), Formula(:T1 of 0413 - :T1 of 0310));

or

dt=Data Table( dt1 ) << Join(

With( Data Table( dt2 ) ),

By Matching Columns( :XNative = :XNative, :YNative = :YNative ),

Copy formula(0),

Suppress formula evaluation(0),

Drop multiples( 0, 0 ),

Include NonMatches(1, 1),

Preserve main table order( 1 ),

Output Table Name( "Contour Plot"))):

Close("Contour Plot", save ("C:\Users\cjariasr\Desktop\cp.txt"));

dt << New Column("subs", Numeric, Continuous, Format("Best", 8), Formula(:T1 of 0413 - :T1 of 0310));

Please help!

Thanks

4 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

One can not completely trouble shoot your code, given that you have not provided what the dt1 and dt2 table names are. I also suggest that you use pointers to the data tables rather than having to deal with all of the code necessary to use the data table's name.  The code below should work with your data, providing that you place into the dt1= and dt2= the names of the two data table. the code also changes the name of the "T1" column in the second data table, so that in your formula for the new column you are creating, you can use the names from the data tables, rather than having to come up with the names JMP will generate when it finds columns from the different tables that have the same name.

Names Default To Here( 1 );

dt1 = Data Table( "first data table" );

dt2 = Data Table( "second data table" );

dt2:t1 << set name("dt2t1");

dt3 = dt1 << Join(

       With( dt2 ),

       By Matching Columns( :XNative = :XNative, :YNative = :YNative ),

       Copy formula( 0 ),

       Suppress formula evaluation( 0 ),

       Drop multiples( 0, 0 ),

       Include NonMatches( 1, 1 ),

       Preserve main table order( 1 ),

       Output Table Name( "Contour Plot" )

);

dt3 << New Column( "subs", Numeric, Continuous, Format( "Best", 8 ), Formula( :T1  - :dt2t1 ) );

Jim
carias0316

New Contributor

Joined:

Jul 16, 2016

Thank you so much!, however I couldn't make it work (this is my first time trying to code anything), probably there is something wrong at the beginning of my code. Here is the whole original code. 

a=("C:\Users\arias\Desktop\0310.txt");

b=("C:\Users\arias\Desktop\0413.txt");

dt1=Open(a

,

Column( "En", Character, Nominal ),

Column( "Name", Character, Nominal ),

Column(

"PT",

Numeric,

Continuous,

Format( "m/d/y h:m:s", 23, 0 ),

Input Format( "m/d/y h:m:s", 0 )

),

Column( "Op", Numeric, Continuous, Format( "Best", 10 ) ),

Column( "ID", Character, Nominal ),

Column( "P", Character, Nominal ),

Column( "Te", Character, Nominal ),

Column( "F4", Character, Nominal ),

Column( "WID", Character, Nominal ),

Column( "St", Numeric, Continuous, Format( "Best", 10 ) ),

Column( "XNative", Numeric, Continuous, Format( "Best", 10 ) ),

Column( "YNative", Numeric, Continuous, Format( "Best", 10 ) ),

Column( "#S", Numeric, Continuous, Format( "Best", 10 ) ),

Column( "Avg", Numeric, Continuous, Format( "Best", 10 ) ),

Column( "N", Numeric, Continuous, Format( "Best", 10 ) ),

Column( "T1", Numeric, Continuous, Format( "Best", 10 ) )

);

End Of Line( CRLF ),

End Of Field( Tab, Comma, CSV( 0 ) ),

Strip Quotes( 1 ),

Use Apostrophe as Quotation Mark( 0 ),

Scan Whole File( 0 ),

Treat empty columns as numeric( 0 ),

CompressNumericColumns( 0 ),

CompressCharacterColumns( 0 ),

CompressAllowListCheck( 0 ),

Labels( 1 ),

Column Names Start( 1 ),

Data Starts( 2 ),

Lines To Read( "All" ),

Year Rule( "20xx" )

);

new window(dt2=Open(b

,

Column( "En", Character, Nominal ),

Column( "Name", Character, Nominal ),

Column(

"PT",

Numeric,

Continuous,

Format( "m/d/y h:m:s", 23, 0 ),

Input Format( "m/d/y h:m:s", 0 )

),

Column( "Op", Numeric, Continuous, Format( "Best", 10 ) ),

Column( "ID", Character, Nominal ),

Column( "P", Character, Nominal ),

Column( "Te", Character, Nominal ),

Column( "F4", Character, Nominal ),

Column( "WID", Character, Nominal ),

Column( "S", Numeric, Continuous, Format( "Best", 10 ) ),

Column( "XNative", Numeric, Continuous, Format( "Best", 10 ) ),

Column( "YNative", Numeric, Continuous, Format( "Best", 10 ) ),

Column( "#S", Numeric, Continuous, Format( "Best", 10 ) ),

Column( "Avg", Numeric, Continuous, Format( "Best", 10 ) ),

Column( "N", Numeric, Continuous, Format( "Best", 10 ) ),

Column( "T1", Numeric, Continuous, Format( "Best", 10 ) )

);

End Of Line( CRLF ),

End Of Field( Tab, Comma, CSV( 0 ) ),

Strip Quotes( 1 ),

Use Apostrophe as Quotation Mark( 0 ),

Scan Whole File( 0 ),

Treat empty columns as numeric( 0 ),

CompressNumericColumns( 0 ),

CompressCharacterColumns( 0 ),

CompressAllowListCheck( 0 ),

Labels( 1 ),

Column Names Start( 1 ),

Data Starts( 2 ),

Lines To Read( "All" ),

Year Rule( "20xx" )

);

dt=New table(Data Table( dt1 ) << Join(

With( Data Table( dt2 ) ),

By Matching Columns( :XNative = :XNative, :YNative = :YNative ),

Copy formula(0),

Suppress formula evaluation(0),

Drop multiples( 0, 0 ),

Include NonMatches(1, 1),

Preserve main table order( 1 ),

Output Table Name( "Contour Plot")))):

dt << update(with(data table (Contour Plot)));

txnelson

Super User

Joined:

Jun 22, 2012

I walked through your code and made some corrections, that I could see.  I don't have your txt files, so I can not test it out completely.  Take a look at the below code, and run each of the steps one at a time, to make sure each step works.

Names Default to Here( 1 );

a = ("C:\Users\arias\Desktop\0310.txt");

b = ("C:\Users\arias\Desktop\0413.txt");

// The following code should open the 0310.txt file and will

// assign a the variable "dt1" as a pointer to the file

// I don't have the txt file to test the code against, but if

// the code is run and it pops up a widow with the data table in it

// you know it is working.  If it doesn't pop up the window, look

// into the Log..........View==>Log

dt1 = Open(

       a,

       Column( "En", Character, Nominal ),

       Column( "Name", Character, Nominal ),

       Column( "PT", Numeric, Continuous, Format( "m/d/y h:m:s", 23, 0 ), Input Format( "m/d/y h:m:s", 0 ) ),

       Column( "Op", Numeric, Continuous, Format( "Best", 10 ) ),

       Column( "ID", Character, Nominal ),

       Column( "P", Character, Nominal ),

       Column( "Te", Character, Nominal ),

       Column( "F4", Character, Nominal ),

       Column( "WID", Character, Nominal ),

       Column( "St", Numeric, Continuous, Format( "Best", 10 ) ),

       Column( "XNative", Numeric, Continuous, Format( "Best", 10 ) ),

       Column( "YNative", Numeric, Continuous, Format( "Best", 10 ) ),

       Column( "#S", Numeric, Continuous, Format( "Best", 10 ) ),

       Column( "Avg", Numeric, Continuous, Format( "Best", 10 ) ),

       Column( "N", Numeric, Continuous, Format( "Best", 10 ) ),

       Column( "T1", Numeric, Continuous, Format( "Best", 10 ) ),

       End Of Line( CRLF ),

       End Of Field( Tab, Comma, CSV( 0 ) ),

       Strip Quotes( 1 ),

       Use Apostrophe as Quotation Mark( 0 ),

       Scan Whole File( 0 ),

       Treat empty columns as numeric( 0 ),

       CompressNumericColumns( 0 ),

       CompressCharacterColumns( 0 ),

       CompressAllowListCheck( 0 ),

       Labels( 1 ),

       Column Names Start( 1 ),

       Data Starts( 2 ),

       Lines To Read( "All" ),

       Year Rule( "20xx" )

);

// The following code should open the 0413.txt file and will

// assign a the variable "dt2" as a pointer to the file

// I don't have the txt file to test the code against, but if

// the code is run and it pops up a widow with the data table in it

// you know it is working.  If it doesn't pop up the window, look

// into the Log..........View==>Log

dt2 = Open(

       b,

       Column( "En", Character, Nominal ),

       Column( "Name", Character, Nominal ),

       Column( "PT", Numeric, Continuous, Format( "m/d/y h:m:s", 23, 0 ), Input Format( "m/d/y h:m:s", 0 ) ),

       Column( "Op", Numeric, Continuous, Format( "Best", 10 ) ),

       Column( "ID", Character, Nominal ),

       Column( "P", Character, Nominal ),

       Column( "Te", Character, Nominal ),

       Column( "F4", Character, Nominal ),

       Column( "WID", Character, Nominal ),

       Column( "S", Numeric, Continuous, Format( "Best", 10 ) ),

       Column( "XNative", Numeric, Continuous, Format( "Best", 10 ) ),

       Column( "YNative", Numeric, Continuous, Format( "Best", 10 ) ),

       Column( "#S", Numeric, Continuous, Format( "Best", 10 ) ),

       Column( "Avg", Numeric, Continuous, Format( "Best", 10 ) ),

       Column( "N", Numeric, Continuous, Format( "Best", 10 ) ),

       Column( "T1", Numeric, Continuous, Format( "Best", 10 ) ),

       End Of Line( CRLF ),

       End Of Field( Tab, Comma, CSV( 0 ) ),

       Strip Quotes( 1 ),

       Use Apostrophe as Quotation Mark( 0 ),

       Scan Whole File( 0 ),

       Treat empty columns as numeric( 0 ),

       CompressNumericColumns( 0 ),

       CompressCharacterColumns( 0 ),

       CompressAllowListCheck( 0 ),

       Labels( 1 ),

       Column Names Start( 1 ),

       Data Starts( 2 ),

       Lines To Read( "All" ),

       Year Rule( "20xx" )

);

// The below code will join the dt1(0310) data table with the

// dt2(0413) data table and set the data table pointer variable

// "dt3" to point to it.  When it runs, it will pop up a new window

// with the new data table in it

dt3 = dt1 << Join(

       With( dt2 ),

       By Matching Columns( :XNative = :XNative, :YNative = :YNative ),

       Copy formula( 0 ),

       Suppress formula evaluation( 0 ),

       Drop multiples( 0, 0 ),

       Include NonMatches( 1, 1 ),

       Preserve main table order( 1 ),

       Output Table Name( "Contour Plot" )

):

// I don't know what you want to do with this......the Join platform in the

// above step wll put the 2 data tables together

dt << update( with( Data Table( Contour Plot ) ) );



Once you have this piece of code working, then you can take the required elements from my first response, and add them into this code.

Jim
carias0316

New Contributor

Joined:

Jul 16, 2016

It worked!, thank you so much and have a great day