error count and fact/dimension tables...

[alexandernatale]

Hello everybody,

I have a problem understanding how "counting" works and in general the relationship between dimension tables and fact tables.

I have a model like in the figure (the model is more complex and the screenshot represents a synthesis).
The table "Product/family/producer" it should be the fact table.

Table "product/family/producer" contains the list of products, by category, by single producer.
Table "list_producer"  is the register of the producers with the various information. In this table, the "producer" field represents the "key", so there is no duplication.

The table "product/family/producer" have 138.744 records.
The table "list_producer" have 25.717 records.

It may happen that a producer present in the "list_producer" table has no products and therefore is not present in the "product/family/producer" table.

Question 1: strange result
=count(producer) --> result: 161.752. WHY?  I expected as  result 138.744 records.

Question 2: count producer that not have product --> 2 ways
=count(distinct {<productId-={0}>}srn) or =count(Aggr(If(Count(DISTINCT productId) > 0, 1), producer)) ?

Thanks for help!

[hic]

You cannot calculate the count of a key without using the 'distinct' clause. It is ambiguous.

Try
Count(distinct producer)

If you want to count the number of producers in the "list_producer" table, you need to create a copy of this field in this table, e.g.
"producer" as "list_producer.producer"
and use Count(distinct "list_producer.producer")

Take a look at "Aggregation of key fields" on https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/AggregationFun....

If you want to count producers that do not have a product, I suggest
Count({<producer=E({<productId={"*"}>} producer)>} distinct producer)

[alexandernatale]

Thanks @hic 

I have another question.
If I wanted to see the list and not the number of producers that do not have products...

I tried this: I created a table with a calculated field which becomes Y if it has at least one product code, otherwise N if the manufacturer has no products.

But if I wanted to have a simple list of only those manufacturers that have no product code, how can I do this?

Thank you!