Often a researcher will have collected data which they wish to use as part of their cohort specification. For example, they may have collected questionnaire data and want to link it with your hosted research datasets.
This requires uploading the researcher’s questionnaire data into the Repository and using it as a source for cohort creation. By following a methodology in which data is stored in the format in which it is provided, researchers dataset can be treated exactly like any other data.
Steps
Step 1- In the cohort configuration right click on the 'Patient Index Table(s)' and select the option to 'Add Catalogue…..' and select the catalogue required.
In this example we are going to do a count of prescriptions bnf 4.7
Once you have added the catalogue and added the bnf filter, right click and select 'edit' from the menu
Step 2 – As we are planning to do an aggregate function we want to use the columns in the 'SELECT' box. CHI will always be present as this is the key linkage. Scroll down the field names in the 'Column' section until you come to the aggregate option (last one) this is defaulted to COUNT but can be changed to any type. 
Click on the + to the left of the field. This will add it to the selection.
Step 3 -
Select the 'Edit' button and this will launch a pop-up box allowing you to edit the aggregate function and to insert the catalogue field name that you are wanting to aggregate.
NB: In this example we are going to do a count of prescriptions 
Select 'OK' this will close the box and allow you to then change the field name Alias if required.
When you close the edit box it will return to the configuration screen. You will notice that the Index table is red. This is because it has not been linked to any of the tables in the main configuration
Step 4: Right click on the catalogue you would like to apply the Patient Index/aggregate to and select 'Edit' from the menu
This allows you to be able to join the index table and specify the type of join. Put a tick in the box next to the index table to join.
At this point I select 'View Sample' this enables me to view the sql code and obtain the index table name 
Step 5: Close the window so the filter from the Index table can be applied.
Right click on the filter container within the Catalogue and create a new filter. If the catalogue does not have a container, please add one.
We want people with more than 1 prescription and we already know the index table is : ix2 and we named the Alias of the count :CountScript
Save this and to check it works right click the catalogue and 'View Sample'. This will also enable you to view the sql code used to build the sample table.
Add Comment