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]

can you post some sample data and the screen shot of your pivot table please.

[#3]

Hi There

It seems that the pivot table is displaying correctly to me-the count(personID) should return the number of rows in the person table.What is wrong with the result?

Erica

[jaimeaguilar]

Hi,

Using Count(personID) will return the number of times that a PersonID appears in rows of your original datasource. Maybe, what you want is to count the unique ocurrences of every PersonID per country and per activity. In that case, you'll need to use Count(DISTINCT IDPERSON) as your expression,

regards

[#5]

Hello,

Thanks for your fast answers.

@jaimeaguilar: I just tried adding the keyword DISTINCT but unfortunately, the number still shows the total of rows in my table.

This screenshot illustrates my issue:

The column Nb personne is not "filtered" by the dimensions in the two left columns. Maybe the function COUNT(...) is not what I should use for that purpose. In my case, the numbers for Liechtenstein should be lower than 100.

Here are my settings for this pivot table.

My dimensions tab:

My expression tab:

I'm wondering if the issue is related to how I imported the data in QlikView. For that, I used the dialog "File > Edit the script" and I selected an external datasource, as follows:

I use QlikView 11.20.12235.0 SR5 (64-bit).

Thanks for your support.

[PrashantSangle]

Hi,

I am not sure but this result you got because of wrong data modeling.

Just check your data model is correct or not??

Regards,

[#7]

Here is my data model from my MSSQL database.

The relations are correctly made, I can query the data from a view in the database:

The grid shows the number of people by activity in Liechtenstein. That would be great to have a similar result in QlikView.

Thanks.

[PrashantSangle]

Hi,

I means check your data model in qlikview.

Is it similar to your above data Model??

Regards,

[#9]

Hi Max,

Thanks for your answer.

Where can I find the data model in qlikview ?

Thanks

[PrashantSangle]

Hi,

Just press ctrl+T

Regards,