More than one field as a join to another table

[#1]

Hi,

I have a predicament where I need to link two tables together but there are two potential fields in one table that I would like to join to the other.

Take the data below for example:

Orders:

Order Number	SalesPerson1	SalesPerson2
12345	123	456
98765	456

Employees:

EmpNo	EmpName
123	Bob Smith
456	John Jones

So for an order there could be 2 (potentially more) sales people working on it. What I want to do is join the Orders table and the Employees table, so that I can select an employee name and see what orders they have worked on. If there was only a single field in the orders table then it would be easy, but a record needs to be returned in the employee appears in either field.

Is it possible to get this to work without duplicating the number of records in the orders table (to make it one row per order/employee)?

Thanks

[gandalfgray]

You can do something like this:

Left Join (Orders)

load EmpNo As SalesPerson1,

     EmpName As SalesPersonName1

resident Employees;

Left Join (Orders)

load EmpNo As SalesPerson2,

     EmpName As SalesPersonName2

resident Employees;

drop table Employees;

[swuehl]

I think it would be best to create a table that shows a record for every relation of Order number to EmpNo (i.e. the solution you excluded in your last paragraph).

If you just don't want to duplicate order lines with other fields, you can create a separate table only containing Order number and EmpNo, that links between Orders table and Employees table.

[awhitfield]

Hi James,

the simplest way would be one line per order / sales person, as attached

HTH Andy

[pratap6699]

orderstbl:

Load orderno,

salesperson1,

salwsperson2

from ---------.xlsx;

LEFT JOIN

Load salesperson1 as emp

,resident orderstbl

LEFT JOIN

Load salesperson1 as emp

,resident orderstbl

employtbl:

Load emp,

ename

from ----path;

[RedSky001 · Accepted solution]

I'd do it as follows.  Yes the order Id is duplicating in the SalesPerson table but I'd rather have the duplicaiton happening in a dimension table than the fact table.

Temp:

load * Inline [ Order Number, SalesPerson1, SalesPerson2

  12345, 123, 456

  98765, 456,

  ];

EmpTable:

load * Inline [EmpNo, EmpName

  123, Bob Smith

  456, John Jones

  ];

DimSalesPerson:

load [Order Number], SalesPerson1 as _Key_EmpID Resident Temp; !

Concatenate (SalesPerson)

load [Order Number], SalesPerson2 as _Key_EmpID Resident Temp;

Inner join (SalesPerson)

load  EmpNo as _Key_EmpID, EmpName as SalesPersonName Resident  EmpTable;

FactOrders:

load [Order Number] Resident Temp;

drop Table EmpTable;

drop Table Temp;

[pratap6699]

great