Many times in reports it will be important to track which programs a participant has recorded a TouchPoint response. If a participant can record a TouchPoint response in multiple programs, the data may become inflated.
For example, in this query, we have pulled in the identity information, flattened TouchPoint information, and Program information, and we're including program names in with the query filter. The intent of this report is to see what programs each participant's TouchPoint response has been recorded in:
Once the data is in the table, we can see that we have several extra lines for some response IDs. We can see that the multiple lines include all the program names that each participant has had an enrollment in.
This is because we pulled in Program Name from the Program Enrollment folder. However, the Program Enrollment folder does not have a reference from any TouchPoint folders in the universe. They are distinct kernels of information. This causes the report to not know entirely which program each response is recorded in, so it pulls in all the possibilities that every participant can have.
But with a couple simple steps with the query filter, we can ensure we only see the programs the responses have been truly recorded in.
Step 1: Bring the Completing Program Unique Identifier_XX
Find the Completing Program Unique Identifier_XX for the TouchPoint you are reporting on into the query filter.
Step 2: Filter on Program Unique Identifier
Set the Completing Program Unique Identifier_XX to Equal to Object from this query
And now find the Program Unique Identifier in the Program Enrollment folder.
Step 3: Run the query
Once this filter is set up along with our other filters, in this case also filtering for Program Name, a Date Range, and only for participants that have a response for this TouchPoint, run the query.
After adding this extra object to the Query Filter, the data now will only pull in the responses where the Completing Program Unique Identifier_XX matches the Program Unique Identifier the report is being filtered for. And now the data is showing a single line for each unique response for this TouchPoint.