Leave a comment at the end of this page or email contact@krishagni.com
How to query data for participants registered in multiple CPs?
You have participants registered under multiple Collection Protocols (CPs). In CP1, only tumor samples are collected, and in CP2, only fluid samples are collected. To query only those participants who are registered in both CP1 and CP2, and exclude others.
This can be achieved by subqueries. Refer https://openspecimen.atlassian.net/wiki/x/aoA-FQ .
You have to create three queries,
Query 1: Fetch all tissue specimens from CP1.
Query 2: Fetch all fluid specimens from CP2.
Query 3: Fetch common participants from CP1 and CP2.
When a participant is registered under multiple collection protocols, their Participant ID is the same across protocols, whereas their PPID varies.
Steps to create Query 1 (Subquery):
Create a ‘New Query’.
Expand the ‘Collection Protocol’ section to add a filter as:
Collection Protocol >> Short Title Equals IU Tissue Collection
Expand the ‘Specimen' section to add a filter as:
Specimen >> Type Contains tissue.
Click on ‘View Records’ and select the columns as required.
Save the query.
To limit your query to a specific protocol, include the CP Short Title in the filters. The CP/CPG chosen in the ‘Choose Collection Protocol / Group’ section does not automatically transfer to subquery results, providing greater flexibility and reusability for your queries.
Steps to create Query 2 (Subquery):
Create a ‘New Query’.
Expand the ‘Collection Protocol’ section to add a filter as:
Collection Protocol >> Short Title Equals IU Blood Collection
Expand the ‘Specimen' section to add a filter as:
Specimen >> Class Equals Fluid
Click on ‘View Records’ and select the columns as required.
Save the query.
Steps to create Query 3 (Main Query):
Create a ‘New Query’.
Expand the ‘Participant' section to add a filter as:
Participant >> Participant ID Is One Of Query 2: Participants of IU Blood Collection
Participant >> Participant ID Is One Of Query 1: Participants from IU Tissue Collection
Expand the ‘Collection Protocol’ section and add a filter as:
Collection Protocol >> Short Title Is One Of ["Tissue CP", "Fluid CP"]
Note: Please apply bracketing correctly for getting desired results. Refer to the screenshot below
To restrict the query results to only the required protocols. In the main query, add a filter to include Collection Protocol short titles.
Click on ‘View Records’ and select the columns as required.
Save the query.