PivotTable issue with expression column

[#1]

Dear Community,

I'm testing QlikView Personal Edition and I'm encountering an issue when I create a pivot table with an expression that does not take into account the dimensions of the table.

I have 3 tables (users, activities and countries) with these relations:

• A user has [one] activity / An activity can have [zero to many] users
• A user has [one] country / A country can have [zero to many] users

Ideally, I wish to have a table that shows the number of user by countries/activities.

I'm able to create a pivot table with 2 dimensions (activities, countries) and I use COUNT(IDPERSON) as expression, where IDPERSON is the primary key of the table users.

The 2 dimensions work fine, countries correctly contain their activities but the column with the expression always displays the number of rows contained by my table users.

Did someone already encounter this issue ?

Many thanks !

[#2]

Here is my data model displayed in QlikView:

It seems there is no relation between the tables. Is there a way to make the relations in this window ?

Best Regards

[PrashantSangle]

Hi,

Yes and that is the issue and because of that you are getting wrong output.

try to create relation between them,

Refer your data model from MSSQL and try to create similar data model in Qlikview.

Regards,

[jonathandienst · Accepted solution]

Hi

Qlikview links tables where one or more fields have the same name in the table. Ideally there should be only one such key field. Like this - replace your PAC_PERSON load with:

     LOAD *,

          PER_COUNTRY As ID_Country,

          PER_ACTIVITY As ACTIVITY_ID;

     SQL SELECT * FROM PAC_PERSON;

Now you will see that the three tables are associated. That should also fix your count problem.

HTH

Jonathan

[#5]

Great ! It works fine now.

The solution was to make two alias for the foreign keys as suggested by Jonathan Dienst. Apparently, QlikView makes the relations between the fields with the same names.

Here is my full import script:

Thanks for your great and fast support.

Best Regards

[PrashantSangle]

Hi,

Hope because of this your original issue also solved.

Regards