Fig 1. Tables one to many relationship
When performing a count on the keys used for joining, we get the correct counts when using two number components.
For example:
Number Component 1: COUNT( TableA."_case_key")
Number Component 2: COUNT( TableB."_case_key")
However, when we combine the counts in a single component, and divide them by each other, then we receive the wrong count.
So the component 3 is wrong:
Number Component 3: COUNT( TableA."_case_key")/COUNT( TableB."_case_key")
We suspect the error might be lying in the way the tables are joined. Please guide us in finding a solution, to get the right division.
Question
Matching percentage when combining tables
We have a question in regards to matching percentages when combining two tables with a one to many relationship.
Fig 1. Tables one to many relationship
When performing a count on the keys used for joining, we get the correct counts when using two number components.
For example:
Number Component 1: COUNT( TableA."_case_key")
Number Component 2: COUNT( TableB."_case_key")
However, when we combine the counts in a single component, and divide them by each other, then we receive the wrong count.
So the component 3 is wrong:
Number Component 3: COUNT( TableA."_case_key")/COUNT( TableB."_case_key")
We suspect the error might be lying in the way the tables are joined. Please guide us in finding a solution, to get the right division.
Fig 1. Tables one to many relationship
When performing a count on the keys used for joining, we get the correct counts when using two number components.
For example:
Number Component 1: COUNT( TableA."_case_key")
Number Component 2: COUNT( TableB."_case_key")
However, when we combine the counts in a single component, and divide them by each other, then we receive the wrong count.
So the component 3 is wrong:
Number Component 3: COUNT( TableA."_case_key")/COUNT( TableB."_case_key")
We suspect the error might be lying in the way the tables are joined. Please guide us in finding a solution, to get the right division.
Hi Paul,
Thanks for reaching out to us! Youre right, when combining the two counts in a single component the tables are implicitly joined according to their connection within the data model. In order to still get the same number of counts as when used in a single component, you need to use PU-functions. The formula would look something like this:
SUM(PU_COUNT(DOMAIN_TABLE (TableA."_case_key"), TableA."_case_key"))
/
SUM(PU_COUNT(TableA, TableB."_case_key"))
This solution worked for me when reconstructing your issue on my local data. If you encounter any issues during implementation, please get back to us!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.