Matching percentage when combining tables

We have a question in regards to matching percentages when combining two tables with a one to many relationship.

Example_Table_Join
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! You’re 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!

1 Like