topic Re: Common Keys (Fields) Between Multiple Tables in QlikView

Common Keys (Fields) Between Multiple Tables

Mon, 13 Oct 2014 19:13:10 GMT

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.

Re: Common Keys (Fields) Between Multiple Tables

Anonymous — Mon, 13 Oct 2014 20:11:07 GMT

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

Re: Common Keys (Fields) Between Multiple Tables

Tue, 14 Oct 2014 10:58:06 GMT

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