topic Re: How to connect two tables? in QlikView

How to connect two tables?

spinchuk — Wed, 19 Mar 2014 18:29:37 GMT

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!

Re: How to connect two tables?

Gysbert_Wassenaar — Wed, 19 Mar 2014 18:36:42 GMT

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;

Re: How to connect two tables?

Wed, 19 Mar 2014 18:49:35 GMT

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;

Re: How to connect two tables?

spinchuk — Wed, 19 Mar 2014 18:50:25 GMT

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!

Re: How to connect two tables?

Gysbert_Wassenaar — Wed, 19 Mar 2014 18:59:43 GMT

Use Ajay's solution

Re: How to connect two tables?

Anonymous — Wed, 19 Mar 2014 19:13:33 GMT

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.

Re: How to connect two tables?

spinchuk — Thu, 20 Mar 2014 14:30:01 GMT

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.