topic Re: Can Virtual Join be done on 3+ Data Tables? in Discussions

Can Virtual Join be done on 3+ Data Tables?

Kelly — Thu, 23 Apr 2020 19:31:02 GMT

I have used virtual join on 2 tables with no issue.

When I try to bring in a third table it will not let me because a column can only have 1 linked reference.

Without adding redundant columns, is it possible to virtual join 3+ data tables?

Thanks for your help.

Re: Can Virtual Join be done on 3+ Data Tables?

P_Bartell — Thu, 23 Apr 2020 19:41:01 GMT

As far as I know there is no maximum number of data tables that can be virtually joined. Here's the online documentation link:

https://www.jmp.com/support/help/en/15.1/index.shtml#page/jmp/virtually-join-data-tables-2.shtml

Re: Can Virtual Join be done on 3+ Data Tables?

Kelly — Sun, 26 Apr 2020 17:35:43 GMT

I went through the documentation and I do not see a way for a virtual join to bring 3 tables together from the same column. This was where I was stuck before because a column can only have 1 linked reference.

Re: Can Virtual Join be done on 3+ Data Tables?

P_Bartell — Sun, 26 Apr 2020 19:00:53 GMT

One key to virtually joining data tables is noting that there is only one link reference column in the main data table, which in turn facilitates virtually connecting to all the other auxiliary data tables which use the link ID column. So it's through this chain of link ID columns that bring all the auxiliary data tables to the main data table.

Did you see the example in the JMP online documentation in the Notes: section on this page? It's an example of virtually joining 3 data tables.

https://www.jmp.com/support/help/en/15.1/#page/jmp/example-of-virtually-joining-columns.shtml#

Re: Can Virtual Join be done on 3+ Data Tables?

Kelly — Sun, 26 Apr 2020 19:33:59 GMT

Yes, in the example there are 3 tables virtually joined together. But the problem is that they use 2 columns for linked reference. See the blue keys in the picture below.

I would like to use only one column to link reference multiple tables.

I know it is possible to virtually join N tables if I use N -1 columns. But I would prefer not to have N - 1 duplicate columns.

Re: Can Virtual Join be done on 3+ Data Tables?

Mandy_Chambers — Mon, 18 May 2020 16:45:59 GMT

Kelly - I assume you saw that in JMP 15 you can now have a Link ID and Link reference on the same column as in this example.
https://www.jmp.com/support/help/en/15.1/#page/jmp/example-of-a-link-id-and-a-link-reference-on-a-single-column.shtml#
This works if the 3 tables have a column that can be linked, one to the other, and sort of look through to pull data back to your referencing table.
As you stated above, you can create a 2nd duplicate column to link, and then hide that column in order to bring in the data you want virtual from multiple tables.