How to create a link between two tables in script

[cliff_clayman]

I have 3 tables that share a field, but not all the values are the same.  I want to JOIN two of the tables to a table that has unique values only in it.  I am looking to do this so that when I create a table or a list box, I can use the field from the unique table so that when a user clicks on the name, the proper data is returned.  I've attached a copy of the QVW that I am working with.  I have qualified each name on the tables because I do not want a JOIN between the Slip and Backlog tables.  I just want to JOIN Slip and Backlog to ProjectManager on the field PM Name.

[cliff_clayman]

I have a related question to this issue.  I manually created the "linked" table called ProjectManager.  Is there a way to create that table in the script from the data in the other two tables?  The values in the table are just the unique PMNames from the other two tables.

[aarkay29]

I did not understand exactly

Do you want to link the tables using a link table which has all the unique PMNames??

[cliff_clayman]

Yes, but I originally manually created that table myself in Excel.  I want to find a way to create it in the script instead.

[aarkay29]

[Link Table]:

LOAD

     [PM Name]

FROM

     [..\PM Names.xlsx](ooxml, embedded labels, table is Backlog);

concatenate ([Link Table])

LOAD

     [PM Name]

FROM

     [..\PM Names.xlsx] (ooxml, embedded labels, table is Slip)

where

     Not Exist([PM Name]);

concatenate ([Link Table])

LOAD

     [PM Name]

FROM

     [..\PM Names.xlsx] (ooxml, embedded labels, table is ProjectManager)

where Not Exist

     ([PM Name]);

This will be your table with all the unique PMNames, Then load each table and link them with [PMName]

[cliff_clayman]

I had to modify it a bit, but I got it to work...thanks!!