Matching 2 tables using Table\Join and Table\Update without successAttached are Tables A, B and C.
Table A has 30 entries by Peter.
Table B has 8,790 entries by Jane.
Table C is the correct joined table done manually.
I want to match the entries by Peter and Jane and create a subset only with "Locations" in Table A, thus Table C.
I had used Table\Join and Table\Update but the results were not accurate.
For example, if you use the Location=-216,161, below should be the output of the joined table ...
Location Peter Jane
-216,161 RDL_Membrane RDL_Membrane
-216,161 RDL_Membrane .
-216,161 Debris Debris
Automation and ScriptingRe: Matching 2 tables using Table\Join and Table\Update without successJoin/update is working correctly as there are multiple matches. You most likely will have to add some value removal to remove those "extra" values.
Re: Matching 2 tables using Table\Join and Table\Update without successHello Jarmo,
Your output is not correct. For Location=-216,161 , Peter has 3 outputs and Jane has 2 outputs. The outcome should be as below. How would you write a jsl to automate it.
Location Peter Jane
-216,161 RDL_Membrane RDL_Membrane
-216,161 RDL_Membrane .
-216,161 Debris Debris
Re: Matching 2 tables using Table\Join and Table\Update without successThat output is correct for a join/update but it might not be what you are looking for.
1. Create duplicate column of Jane to Table B
2. Use that new column (Column 3 in my case) as matching column for update
3. Select Location and Jane columns, Rows > Row Selection > Delete Duplicate
4. Select only Jane column (click column header (sometimes you have to hold alt)) and delete those values (press delete)
Re: Matching 2 tables using Table\Join and Table\Update without successI am looking to do the above by jsl. Also, the data set will change and there will be times when Jane's column will be a duplicate and that is fine.
Re: Matching 2 tables using Table\Join and Table\Update without successI would most likely attempt doing this as a workflow. You can of course go directly with JSL if you can write such a script
Re: Matching 2 tables using Table\Join and Table\Update without successWould you consider first changing the formatting of your data? In my experience, sometimes it is easier to reformat the data into a more analysis-friendly form, rather than to use complex scripting to adapt the analysis to your data format. Having multiple rows corresponding to a particular location (Tall format) works in many cases, but becomes tricky when there are also duplicate observations (i.e. multiple rows that are completely identical). JMP does not always know how it should handle such duplicates, so it may not give you the results you expect when joining.
For example: would you consider changing the format from Tall to Wide? Something like this is common in, for example, semiconductor inspection/test data when there are multiple error categories or yield ranks:
View more...
Local( {obj},
obj = Data Table( "Table A" ) << Tabulate(
Add Table(
Column Table(
Grouping Columns( :Peter ),
Add Aggregate Statistics( :Peter )
),
Row Table( Grouping Columns( :Location ) )
)
);
obj << Make Into Data Table;
obj << Close Window;
);
Resulting in a table like this:
Combining tables in this format for both operators would be much easier.
Another option you might consider is the Multiple Response modeling type. For example:
View more...
dt=Data Table( "Table A" ) << Split(
Split By( :Peter ),
Split( :Peter ),
Group( :Location ),
Output Table( "Split of Table A by Peter" ),
Sort by Column Property
);
// New formula column: Concatenate[BL_Twin,Debris,Good_FS,Probemarks,RDL_Membrane,RDL_SpotsDark]
dt <<
New Formula Column(
Operation( Category( "Character" ), "Concatenate with Comma" ),
Columns(
:BL_Twin, :Debris, :Good_FS, :Probemarks, :RDL_Membrane, :RDL_SpotsDark
)
) << Run Formulas;
dt:"Concatenate[BL_Twin,Debris,Good_FS,Probemarks,RDL_Membrane,RDL_SpotsDark]"n <<
Set Modeling Type( "Multiple Response" );
which looks like this:
