Linking multiple fields in one table to a single field in another

[dukane24]

Hello,

I have a situation where I've got one large table that has fields like this:

Project ID

Project Description

Role 1

Role 2

Role 3

Role 4

The role fields have individual's names in them.  I've got another table that has fields like this:

Name

Role type

Hours worked

I'm trying to figure out how to link the two tables - the Role fields contains individual names, and I need to match them up with hours worked - the problem is that the first table doesn't have one name field, it's got four of them.  I think doing something like loading the second table four times with four different table names would work, but it seems awfully crude.

If someone could offer insight into how this might be accomplished, that would be awesome.

Thank you!

[Anil_Babu_Samineni · Accepted solution]

May be this?

LargeTable:

Load *, Autonumber([Role 1] & [Role 2] & [Role 3] & [Role 4]) as %Key;

Load Distinct [Project ID],

[Project Description],

[Role 1],

[Role 2],

[Role 3],

[Role 4]

From <Data Source>;

AnotherTable:

Load *, Autonumber(Rowno(), [Role type]) as %Key;

Load Name,

[Role type],

[Hours worked]

From <Data Source>;

[Link Table]:

Load Distinct *, %Key Resident LargeTable;

Concatenate([Link Table])

Load Distinct *, %Key Resident AnotherTable;

Drop Fields [Role 1], [Role 2], [Role 3], [Role 4] from LargeTable;

Drop Field [Role type] from AnotherTable;