topic Re: PivotTable issue with expression column in QlikView

PivotTable issue with expression column

Tue, 25 Mar 2014 22:31:00 GMT

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 !

Re: PivotTable issue with expression column

Tue, 25 Mar 2014 22:35:24 GMT

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

Re: PivotTable issue with expression column

Tue, 25 Mar 2014 23:00:12 GMT

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

Re: PivotTable issue with expression column

jaimeaguilar — Tue, 25 Mar 2014 23:34:01 GMT

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

Re: PivotTable issue with expression column

Wed, 26 Mar 2014 07:35:25 GMT

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.

Re: PivotTable issue with expression column

PrashantSangle — Wed, 26 Mar 2014 07:41:49 GMT

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,

Re: PivotTable issue with expression column

Wed, 26 Mar 2014 08:00:44 GMT

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.

Re: PivotTable issue with expression column

PrashantSangle — Wed, 26 Mar 2014 08:05:58 GMT

Hi,

I means check your data model in qlikview.

Is it similar to your above data Model??

Regards,

Re: PivotTable issue with expression column

Wed, 26 Mar 2014 08:08:34 GMT

Hi Max,

Thanks for your answer.

Where can I find the data model in qlikview ?

Thanks

Re: PivotTable issue with expression column

PrashantSangle — Wed, 26 Mar 2014 08:10:28 GMT

Hi,

Just press ctrl+T

Regards,

Re: PivotTable issue with expression column

Wed, 26 Mar 2014 08:14:58 GMT

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

Re: PivotTable issue with expression column

PrashantSangle — Wed, 26 Mar 2014 08:19:28 GMT

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,

Re: PivotTable issue with expression column

jonathandienst — Wed, 26 Mar 2014 08:24:44 GMT

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

Re: PivotTable issue with expression column

Wed, 26 Mar 2014 09:40:07 GMT

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

Re: PivotTable issue with expression column

PrashantSangle — Wed, 26 Mar 2014 09:45:05 GMT

Hi,

Hope because of this your original issue also solved.

Regards