287047_en-US

liliconcom

How to create a dt3 with dt1 all rows, plus dt2 rows which not match with "Id" column with dt1, result only show out few columns in dt1 and dt2

I Want to get the result of: compare dt1 with dt2, if there are "Id" don't match then create a new table dt3 with dt1 all rows, and add the don't match rows in dt2 into dt1, and pick 3 columns(name, lot, EPval) show out in dt3. thanks a lot

dt1 = New Table( "test",
 addrows( 3 ),
 New Column( "lot", character, values( {"K1", "K2", "K3"} ) ),
 New Column( "Id", numeric, values( {"11889922", "11776620", "22889955"} ) ),
 New Column( "EPval", numeric, values( {1, 2, .} ) ),
 New Column( "Name", character, values( {"John", "Joe", "PP"} ) )
);
dt2 = New Table( "test",
 addrows( 3 ),
 New Column( "lot", character, values( {"K1", "K2", "K3"} ) ),
 New Column( "Id", numeric, values( {"33888822", "11776620", "22889955"} ) ),
 New Column( "EPval", numeric, values( {1, 2, .} ) ),
 New Column( "Mgr", character, values( {"J", "H", "P"} ) )
);

Data TableRe: How to create a dt3 with dt1 all rows, plus dt2 rows which not match with "Id" column with dt1, result only show out few columns in dt1 and dt2

thanks for your reply. But do you run the script? It doesn't work in my PC. I am use JMP14, not sure if it is because of this. the dt3 not pop up

Re: How to create a dt3 with dt1 all rows, plus dt2 rows which not match with "Id" column with dt1, result only show out few columns in dt1 and dt2

Apparently, I inadvertently deleted the "With" element of the merge.  Not sure how I did it.....but....here is the corrected code

/*I Want to get the result of: compare dt1 with dt2, if there are "Id" don't match then create a new table dt3 with dt1 all rows, and add the don't match rows in dt2 into dt1, and pick 3 columns(name, lot, EPval) show out in dt3. thanks a lot */
dt1 = New Table( "test",
 addrows( 3 ),
 New Column( "lot", character, values( {"K1", "K2", "K3"} ) ),
 New Column( "Id", numeric, values( {"11889922", "11776620", "22889955"} ) ),
 New Column( "EPval", numeric, values( {1, 2, .} ) ),
 New Column( "Name", character, values( {"John", "Joe", "PP"} ) )
);
dt2 = New Table( "test",
 addrows( 3 ),
 New Column( "lot", character, values( {"K1", "K2", "K3"} ) ),
 New Column( "Id", numeric, values( {"33888822", "11776620", "22889955"} ) ),
 New Column( "EPval", numeric, values( {1, 2, .} ) ),
 New Column( "Mgr", character, values( {"J", "H", "P"} ) )
);

// I assumed that there 
dt3 = dt1 << Join( with(dt2),
 By Matching Columns( :Id = :Id ),
 Drop multiples( 0, 0 ),
 Include Nonmatches( 1, 1 ),
 Preserve main table order( 1 )
);

 

Re: How to create a dt3 with dt1 all rows, plus dt2 rows which not match with "Id" column with dt1, result only show out few columns in dt1 and dt2

 

This is the log still don't work. I may post another example to ask this question. IF the second example work then we can ignore this example.

 

 

//:*/
dt3 = dt1 << Join(
By Matching Columns( :Id = :Id ),
Drop multiples( 0, 0 ),
Include Nonmatches( 1, 1 ),
Preserve main table order( 1 )
);
/*:

Join with Data Table unknown{3}

Re: How to create a dt3 with dt1 all rows, plus dt2 rows which not match with "Id" column with dt1, result only show out few columns in dt1 and dt2

All you need to do is to use

     Tables==>Join

and specify to "Include non-matches" for both the Main and With table

/*I Want to get the result of: compare dt1 with dt2, if there are "Id" don't match then create a new table dt3 with dt1 all rows, and add the don't match rows in dt2 into dt1, and pick 3 columns(name, lot, EPval) show out in dt3. thanks a lot */
dt1 = New Table( "test",
 addrows( 3 ),
 New Column( "lot", character, values( {"K1", "K2", "K3"} ) ),
 New Column( "Id", numeric, values( {"11889922", "11776620", "22889955"} ) ),
 New Column( "EPval", numeric, values( {1, 2, .} ) ),
 New Column( "Name", character, values( {"John", "Joe", "PP"} ) )
);
dt2 = New Table( "test",
 addrows( 3 ),
 New Column( "lot", character, values( {"K1", "K2", "K3"} ) ),
 New Column( "Id", numeric, values( {"33888822", "11776620", "22889955"} ) ),
 New Column( "EPval", numeric, values( {1, 2, .} ) ),
 New Column( "Mgr", character, values( {"J", "H", "P"} ) )
);

// I assumed that there 
dt3 = dt1 << Join( with(dt2),
 By Matching Columns( :Id = :Id ),
 Drop multiples( 0, 0 ),
 Include Nonmatches( 1, 1 ),
 Preserve main table order( 1 )
);

Re: How to create a dt3 with dt1 all rows, plus dt2 rows which not match with "Id" column with dt1, result only show out few columns in dt1 and dt2

The code you are showing is not the corrected code I posted.  The corrected JOIN code is

dt3 = dt1 << Join( with(dt2),
 By Matching Columns( :Id = :Id ),
 Drop multiples( 0, 0 ),
 Include Nonmatches( 1, 1 ),
 Preserve main table order( 1 )
);