How to count a value in a table with association

[ducduc1993]

Hello everybody,
I'm newbie. I have a problem when count value in a table with association. I have main table "orders" with 20246 lines.

And here is the result when I count  Product.ID

After association with table "Product"

, there were a total of 20251 , 5 more than the old number. 

The error here is that the "count" function calculate adds ProductIDs that do not appear in the orders table, such as Productid 90000 or 90009 (The number of occurrences is only 1).

How can i use function"count" to calculate occurrences of each ProductId only appears in the table "orders" after association?

Thanks?

[Dataintellinalytics]

Hi,

Since both the tables are joined via ProductID it is expected behavior of Qlik to return 20251.

You can override this in two ways.

1. Create a flag to differentiate tables example create a field in load statement like 

"Orders" as Source then use this in expression wherever you count ProductID like

Count( {<Source={'Orders'}>} ProductID )

2. Create duplicate column say ProductID as Order_ProductID and use this field wherever you count ProductID like  Count(Order_ProductID)

[ducduc1993]

Thank you for your answer. I understand way 2, and it worked but how can I do way 1. I'm just starting to get acquainted with qlik.

[marcus_sommer]

You don't need mandatory an extra field else just referring to an order-table unique field should be sufficient, like:

Count( {<OrderID={"*"}>} ProductID)

In many scenarios you don't need such approaches if the dimension-tables are filtered against the facts, for example with something like:

load * from Products where exists(ProductID);

which has also the benefit not to show all already outdated dimension-values.