In a Discovery, how to identify records that do not have a corresponding child record
Hello,
Here is the scenario. We have insurance policies in a policy table which joins to a coverage table by a policy_id primary key which is a foreign key in the coverage table. A simplified ER diagram would look something like this:
In a Discovery we would like identify all policies that do not have a coverage record with the CoverageCode of "COLLISION". Essentially we want to list all policies that do not have collision coverage attached to them.
We are using a direct query data model and would like to know if doing this is possible within a Discovery.
Thanks,
Rob Clark
2 replies
-
Hi , There are probably several ways to achieve the required result. One option in Pyramid 2023 or higher is to use the 'text' aggregation function in model then searching the resulting string for the CoverageCode of 'COLLISION'. Here is how..
I created a simple dataset as described in your question.
Using model I imported this data into an IMDB (direct query would work the same way) In the columns section of model I used a TEXT aggregation of the Coverage Code field
This will give the following result in Discover with the CoverageCodes concatenated into a string
Then I created a new measure formulate which searches for the text 'COLLISION'
Giving the following result..
Finally filtering the result set to only show the POLICY IDs that do not contain 'COLLISION'.
This could easily be extended to use a parameter input instead of hard coding 'COLLISION'