Common Keys (Fields) Between Multiple Tables

[#1]

Hi, I'm new to QlikView and i need help for my scenario, let me give a simple example.

I have excel sheet with 4 tables Employees, Managers, Customers, and Users

* All of these tables has Gender, Age, ID Number as common fields

* But, Employees and Managers tables has a common SSN field

* And Customers and Users has a common Full Name field

Now, after loading data into qlikview the following table association is created

and the following is my design

The Problem : When trying to filter records by gender, the synthesis keys produce a problem that will separate the table to 2 groups and that because each 2 tables have a common field.

The result after filter will be

Actually, i can make a workaround by renaming fields or comment them to avoid synthesis keys , but i need to be able to filter all tables by gender or/and age range or by selecting the ID Number.

Does anybody faced this problem, what is the best solution ?

Thanks.

[Anonymous]

Hi,

concatenate your tables in one table:

Facts:

LOAD RowNo() as EmployeeCount, [Employee Name] as Name,

     Gender,

     Age,

     [ID Number],

     [SSN],

     'Employee' as Type

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Employees);

concatenate(Facts)

LOAD RowNo() as ManagerCount, [Manager Name] as Name,

     Gender,

     Age,

     [ID Number],

     [SSN],

     'Manager' as Type

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Managers);

concatenate(Facts)

LOAD RowNo() as CustomerCount,[Customer Name] as Name,

     Gender,

     Age,

     [ID Number],

     [Full Name],

     'Customer' as Type   

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Customers);

concatenate(Facts)

LOAD RowNo() as UserCount, [User Name] as Name,

     Gender,

     Age,

     [ID Number],

     [Full Name],

     'User' as Type

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Users);

hope this helps.

Best regards

Stefan

[#3]

why don't you try with the linktable to remove the synthetic key.