Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

How to prevent a join in the model

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

7 Replies
Partner
Partner

Re: How to prevent a join in the model

Hi Nasser,

any chance that you can upload a sample QVW?

Also try Count(DISTINCT key_project)

Andy

Not applicable

Re: How to prevent a join in the model

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

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
Contributor III

Re: How to prevent a join in the model

then rename that key_project name in other table.

then u get 1

Not applicable

Re: How to prevent a join in the model

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

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
Contributor

Re: How to prevent a join in the model

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