topic Re: How to prevent a join in the model in QlikView

How to prevent a join in the model

Wed, 25 Nov 2020 16:16:04 GMT

hello,

There is a behavior that I don't understand.

I have a "projects" table where a projet is unique. and I have "results" table where this project has 2 results.

the "key_projet" field exists in both tables.

I created a chart with key_projet dimension and the expresssion is count(key_projet).

I expected a count "1" but it is "2". It seems that a join is made between two tables but it is not I would like.

I don't know why ?

thanks a lot in advance for your help

Nasser

Re: How to prevent a join in the model

awhitfield — Mon, 27 Mar 2017 10:10:56 GMT

Hi Nasser,

any chance that you can upload a sample QVW?

Also try Count(DISTINCT key_project)

Andy

Re: How to prevent a join in the model

Mon, 27 Mar 2017 11:38:09 GMT

Hi Andy,

thanks for your quick answer.

It works. But I have an additional question.

In the chart I added an expression "=sum(amount)" and the amount is doubled"

Re: How to prevent a join in the model

Peter_Cammaert — Mon, 27 Mar 2017 11:41:42 GMT

Do these amounts appear twice in your results table? Then a sum() will double the original amount.

And yes, connected tables in your data model will automatically perform a Natural Join.

Re: How to prevent a join in the model

stonecold111 — Mon, 27 Mar 2017 11:45:05 GMT

then rename that key_project name in other table.

then u get 1

Re: How to prevent a join in the model

Mon, 27 Mar 2017 11:51:29 GMT

Hello Peter,

thanks your feed-back.

No, the amount is held in the project table. So in SQL I can notice that there is only one projet with its amount in the projects table. In a SQL query, I select both (project and amount) only from the projects table. There are 2 records for this project in the result table so (by sql) the result is doubled if I join this result table. And That make sens. But in Qlik, the chart has been build only on project number (coming from projects table) and sum(amount) (expression) coming from projects table as well. In this case, doubled does not make sens.

Re: How to prevent a join in the model

Peter_Cammaert — Mon, 27 Mar 2017 12:01:31 GMT

Except if your project definitions appear twice in your projects table as well.

Add a new column to your projects table called TmpKeyProjet that contains a copy of key_projet. Reload and add a straight table with dimension TmpKeyProjet and expression =count(TmpKeyProjet)

Re: How to prevent a join in the model

aapurva09 — Mon, 27 Mar 2017 12:34:05 GMT

Hi,

The key field i.e. 'key_project' should not be used anywhere in the front end as a best practice. Because it is a key and connecting 2 tables which can have different frequencies. The solution is to count the projects by renaming the Project column from Table 'Projects'.

Thanks