Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
ducduc1993
Contributor
Contributor

How to count a value in a table with association

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.

Main table.jpg

And here is the result when I count  Product.ID

ducduc1993_1-1712230936914.png


After association with table "Product"

ducduc1993_3-1712231722620.png

 

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

ducduc1993_2-1712231280129.png

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?

Labels (1)
3 Replies
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
Contributor
Contributor
Author

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.