How to connect two tables?

[spinchuk]

I'm drawing blank on what seems to be a simple concept...

Let's say we have 2 tables: FactSales and DimEmployee:

ConsultantKey	SalesPersonKey	Value
1	2	10
3	2	10
1	3	10

EmployeeKey	EmployeeName
1	John Smith
2	Ann Smith
3	Bob Write

What is the best way to connect these two tables?

Both ConsultantKey and SalesPersonKey correspond to the EmployeeKey.

Thank you!

[Gysbert_Wassenaar]

The best way is to not connect them, but to map the keys to names:

EmpMap:

mapping load EmployeeKey, EmployeeName

From DimEmployeeSource;

FactSales:

Load *

     , applymap('EmpMap',ConsultantKey) as ConsultantName

     , applymap('EmpMap',SalesPersonKey) as SalesPersonName

From FactSalesSource;

[#3]

May be you can try creating three tables in total like this;

Fact:

Load ConsultantKey,

         SalesPersonKey,

         Value

From Table;

Dim1:

Load EmployeeKey as ConsultantKey,

        EmployeeName as ConsultantName

From Table;

Dim2:

Load EmployeeKey as SalesPersonKey,

        EmployeeName as SalesPersonName

From Table;

[spinchuk]

Thank you Gysbert,

while your solution works for the example above, my question is more about the modeling concept, since I have many other attributes of employee (such as employee type, tier, cost, state, etc.). So mapping everything into the fact table does not seem to make sense. Any other suggestion?

Thank you!

[Gysbert_Wassenaar]

Use Ajay's solution

[Anonymous]

I think that the solution that Henric Cronström suggested for the calendar can be used in this case for Employee:

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

User can select an employee name, and a flag, let's call it "Role", specifying that he wants to see the facts records of this employee as a Consultant or as a Sales Person.

[spinchuk]

Thank you everyone for your recommendations!

The solution that I chose is to split DimEmployee table into two: Consultant and SalesPerson.

Please find the code attached (thank you Igor!)

Please comment if you feel that there is yet another solution.