How to prevent a join in the model

[#1]

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

[awhitfield]

Hi Nasser,

any chance that you can upload a sample QVW?

Also try Count(DISTINCT key_project)

Andy

[#3]

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"

[Peter_Cammaert]

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.

[stonecold111]

then rename that key_project name in other table.

then u get 1

[#6]

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.

[Peter_Cammaert]

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)

[aapurva09]

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