5 Using Advanced Conditions to Create Queries on Insight Mart

5.1 Creating a New Advanced Condition

To start a new Insight Mart advanced condition:

1. Navigate to Queries > Advanced Conditions > New (Insight Mart).

   The ADVANCED CONDITIONS EDITOR page appears with the data source.

   Note:

   You may also start an advanced condition by converting a QBE or a filter value set to an advanced condition. See "Converting a QBE to an Advanced Condition" and "Converting a Value Set to an Advanced Condition" for details.

2. Enter the attributes (fields and values) for the advanced condition.

3. Click the context menu icon (Add Advanced Condition Element Above/Below icon).

   In the context menu, the categories (Activities, Additional Info, Analysis, Events, General, Patients, and Products) represent the Argus case form tabs. In these categories, the data mart fields that pertain to case information are organized in this hierarchy:

   • Argus case form tab

   • Sections within the tab

   • Fields within the section

   You may browse through the appropriate hierarchy and select the required field as an advanced condition attribute.

   The AC Library category lists all the advanced conditions stored in the system. You may browse through the AC Library category and select a stored advanced condition as an attribute for the new advanced condition.

   
   

4. Browse through a category hierarchy, and select the required attribute.

   The selected attribute appears as a row in the editor.

   Note:

   The user defined fields in Argus Safety which have been converted as a look up are also available as attributes for the advanced condition search.

5. In the Value field, enter the value for the selected attribute.

   Depending on the type of the selected attribute, the Value field populates a:

   • Drop-down list to select from a set of predefined values

   • Text box to enter text strings or numerals

   • Date field to enter dates

   • Browser to select MedDRA dictionary terms, company products, WHO drugs, clinical study IDs, Drugs in Study (incl. Comparators) and Investigational Drugs.

     To use a browser to populate the Value field, click the ellipsis button next to the Value field. From the context menu, select the option corresponding to the browser you want to start. For example, you can start the MedDRA Browser and enter criteria to search for a preferred term based on a specific code or description. You can add a selected term to the Value field.

6. Click the Conditions field, and select a condition for the attribute value.

   See Table 5-1 for a description of each condition. The availability of the conditions depends on the selected attribute.

   Table 5-1 Conditions for Defining an Advanced Condition

   Condition	Select this option if you want to…
   equal to	Retrieve cases where the selected attribute's value is equal to what the Value field specifies
   not equal to	Retrieve cases where the selected attribute's value is not equal to what the Value field specifies
   greater than	Retrieve cases where the selected attribute's value is greater than what the Value field specifies
   greater than or equal to	Retrieve cases where the selected attribute's value is greater than or equal to what the Value field specifies
   less than	Retrieve cases where the selected attribute's value is less than what the Value field specifies
   less than or equal to	Retrieve cases where the selected attribute's value is less than or equal to the Value that the field specifies
   missing	Retrieve cases where the selected attribute's value has not been specified
   exists	Retrieve cases where the selected attribute has any value
   begins with	Retrieve cases where the selected attribute's value begins with what the Value field specifies
   contains	Retrieve cases where the selected attribute's value contains what the Value field specifies
   does not contain	Retrieve cases where the selected attribute's value does not contain what the Value field specifies
   in	Retrieve cases where the selected attribute's value exists in what the Value field specifies
   not in	Retrieve cases where the selected attribute's value does not exist in what the Value field specifies

   
   

   Tip:

   To change the structure of the query by changing the order of the attribute rows, select a row and then click the arrow icons to move the row up or down. To delete a row from the editor, select the row and click the Delete icon.

7. Specify the operator.

   Use the Operator drop-down list to specify the Boolean or Set operator to join the attribute with another attribute.

8. To add more attributes to your advanced condition, click the icons on the left bar to insert another attribute above or below the existing attribute.

9. Specify runtime parameters.

   Note:

   Argus Insight lets you create advanced conditions which when executed, ask for user-specified values for certain attributes to generate the case series. For example, you may create an advanced condition in which the Case Number or the Product name may be specified only at the time of execution. In this case, the advanced condition would only retrieve those cases where the Case Number/Product Name is as specified at the time of execution.

   Creating an advanced condition using same field name multiple times requires to change the Parameter name so that while executing the advanced condition different parameters values can be entered.

10. Identify the attribute row to specify runtime parameters.

11. Click the ellipsis button next to the Value field, and then select Parameters from the menu.

    
    

12. Click Parameter.

    The Parameter dialog box appears.

13. To pass multiple runtime values for a single attribute, select the Multi Select check box.

    For example, you might want to pass more than one case number or product name as runtime parameters.

14. Click OK.

    If the field is configured to accept single runtime parameter, the field you configured as a runtime parameter is displayed in the Value text box enclosed within % symbol. For example, %Product Type%.

    If the field is configured to accept multiple runtime parameters, the field name appears in the Value text box enclosed within %% symbol. For example, %%Product Type%%.

15. To determine the order of execution for the selected advanced conditions, enter parentheses in the ( and ) columns.

16. Examine the advanced condition results.

17. Click Execute.

    Tip:

    To cancel the query execution at this point and return to the Advanced Conditions Editor, click Cancel Query.

    • If any runtime parameter is configured, the Parameters dialog box appears with all the fields available based on the categorization of different fields from the tree view.

      • Specify the parameter values by using the options in the dialog box, and click Execute.

        Select at least one parameter for query execution.

        The ACTIVE CASE SERIES page appears with the matching cases from the data mart.

        Tip:

        If you select one runtime parameter and execute the search, you must enter a field.

        If you select multiple runtime parameters and execute the search, you have the option to ignore the runtime parameters.

      • To ignore the field criteria for query execution, select All.

      • To save the selected values on the Advanced Condition Parameters screen, click Save.

        Note:

        • When the Save button is clicked, the values of the current Advance Condition only are saved, and not for the Nested Advance Conditions.

        • The Save button is enabled only when Advance Condition is saved, and not when the Advanced Condition is executed from Query By Example, Filter, or Advance Condition Library page.

        You may enter value for one or more parameters.

        If no value is selected for any parameter that is if you try to save a parameterized advanced condition without providing any value, then an error message appears as 'You must select values in order to save.'

        All the fields where value is been provided are stored for the future use.

      • Upon execution of these advanced conditions again, the previously selected values will appear by default on the Advanced Condition Parameters page.

        Note:

        On the Advanced Condition Editor page, the parameterized advanced conditions appears in their original structure, and the saved parameter values are not available. These values appear only while executing the advanced condition.

      • Query criteria for the case series generated using such advanced conditions will only display fields for which values for the parameter were provided.

      • The selective use of parameters can also be used while converting existing filters into advanced conditions.

    • If no cases are found that match the querying criteria, a warning message appears.

      Click View Query to return to the Advanced Conditions Editor, modify the criteria and execute again.

    • If the cases that match the query criteria are found, the ACTIVE CASE SERIES page appears with the list of such cases.

18. Examine the case series. If the case series is too large, you may want to modify the advanced condition by specifying additional attributes or changing values.

    Alternatively, if you find the case series to be appropriate, you can save the advanced condition to the system.

    The system retrieves the matching cases from the data mart and displays them in the ACTIVE CASE SERIES page.

    Note:

    If the Argus Insight case series are created by using the Argus Mart advanced condition, then these case series records only case IDs, case revision effective start dates, and query criteria (as a text string).

SELECT 
table1.common_column
FROM
Table1, table2, table3
WHERE 
Table1.column2 = table2.column2
AND 
Table2.column3 = table3.column3 
INTERSECT 
SELECT 
Table4.common_column
FROM
Table4, table5, table6
WHERE
Table4.column2 = table5.column2
AND
Table5.column3 = table6.column3

WHERE Country = "Australia" AND Co-Drug Code w Study = "LAS + TAB" OR Co-Drug Code w Study = "LAS+" AND Overdose = "Yes" AND Interaction? = "Yes"

WHERE Country = "United States" OR Country = "Australia"

SELECT DISTINCT v_rpt_case.case_id
FROM v_rpt_case, rpt_product
WHERE v_rpt_case.case_id = rpt_product.case_id
AND ( (v_rpt_case.country_id = 13)
AND ( UPPER (rpt_product.co_drug_code) = 'LAS+TAB'
OR (pat_exposure IN (
SELECT product_id
FROM lm_product
WHERE UPPER (lm_product.drug_code) ='LAS+TAB')))
OR (UPPER (rpt_product.co_drug_code) = 'LAS+'
OR (pat_exposure IN (SELECT product_id FROM lm_product
WHERE UPPER (lm_product.drug_code) ='LAS+')))
AND (rpt_product.is_overdose = 1)
AND (rpt_product.interaction = 1))
INTERSECT
SELECT DISTINCT v_rpt_case.case_id
FROM v_rpt_case
WHERE v_rpt_case.case_id IN (
SELECT DISTINCT v_rpt_case.case_id
FROM v_rpt_case
WHERE (v_rpt_case.country_id = 13)
OR (v_rpt_case.country_id = 223))

(SELECT DISTINCT v_rpt_case.case_id                FROM v_rpt_case                WHERE v_rpt_case.case_id IN (                                              SELECT DISTINCT v_rpt_case.case_id                                         FROM v_rpt_case                                         WHERE (v_rpt_case.case_num IS NOT NULL)))
UNION
(SELECT DISTINCT v_rpt_case.case_id                FROM v_rpt_case, rpt_event, rpt_product                WHERE v_rpt_case.case_id = rpt_event.case_id                AND v_rpt_case.case_id = rpt_product.case_id                AND ((rpt_event.agent_seq_num = 1)))

SELECT DISTINCT v_rpt_case.case_id              
FROM v_rpt_case, rpt_product
               WHERE v_rpt_case.case_id = rpt_product.case_id
                 AND ( (UPPER (rpt_product.co_drug_code) = 'RELVAC1'
                              OR (pat_exposure IN (
                                   SELECT product_id
                                   FROM lm_product
                                   WHERE UPPER (lm_product.drug_code) = 'RELVAC1')
))
OR ( UPPER (rpt_product.co_drug_code) = 'C_TEST DRUG 1'
OR (pat_exposure IN (
        SELECT product_id
            FROM lm_product
            WHERE UPPER (lm_product.drug_code) = 'C_TEST DRUG 1')
)
)
)
INTERSECT
(SELECT DISTINCT v_rpt_case.case_id
                 FROM v_rpt_case
                WHERE (v_rpt_case.country_id = 81)
UNION
SELECT DISTINCT v_rpt_case.case_id
              FROM v_rpt_case, rpt_followup
              WHERE v_rpt_case.case_id = rpt_followup.case_id
                 AND (( (TRUNC (rpt_followup.receipt_date) =
TO_DATE ('17-MAR-2009', 'DD-MON-YYYY')
)
                 AND rpt_followup.seq_num > 0
)
)
UNION
SELECT DISTINCT v_rpt_case.case_id
               FROM v_rpt_case, rpt_reporters
               WHERE v_rpt_case.case_id = rpt_reporters.case_id
                   AND ((v_rpt_case.state_id = 3))
                   OR (rpt_reporters.primary_contact = 1))

5.2 Working with the Last Modified or Saved Advanced Condition

The system assigns an active status to a advanced condition when you modify and save it or when you execute it. Unless you save the modifications to another advanced condition or execute another advanced condition, the last advanced condition you modified or executed remains active. This is helpful in situations when you want to access a frequently used advanced condition.

To work with last modified or saved advanced condition:

• For Argus Insight database, select Active (Insight Mart)

• For Argus Mart database, select Active (Argus Mart)

To view an active advanced condition:

1. Navigate to Queries > Advanced Condition > Active (Insight Mart).

   The ACTIVE ADVANCED CONDITIONS EDITOR page appears with data source and the advanced condition you executed or modified last.

2. If the active advanced condition was saved to the system before executing, the name of the advanced condition appears next to the Active Query Name and in the Name field.

   However, if the active advanced condition was not saved to the system before executing, the text <Not Saved> is displayed next to the Active Query Name label.

   You can enter the name of the advanced condition in the Name field and click Save.

   To rename an already saved advanced condition, in the Name field, enter the new name, and click Save.

   From the ACTIVE ADVANCED CONDITION page, you can perform the following tasks:

   Task	Description
   Modify the attributes in the Advanced Conditions Editor	If required, you can modify the attributes (fields and values) in the Advanced Conditions Editor. See "Creating a New Advanced Condition" for more information. Click Save to save the changed field values. This button is only available for a saved active advanced condition.
   Save Active Advanced Condition with another name	Click Save As to save the active advanced condition by a different name. The ADVANCED CONDITIONS LIBRARY page lists all the advanced conditions saved to the system. See "Working with Saved Advanced Conditions" for more information.
   View the Advanced Condition in SQL	Click View SQL to view the underlying SQL query for the advanced condition. In this SQL, manually replace the V_RPT_CASE with RPT_CASE. This is required to execute the query in the Oracle database.
   Assign Permissions	Click Permissions to set the group-level access permissions on the advanced condition. See "Creating a New Advanced Condition" for more information. The Permissions button is only available for saved active advanced conditions.
   Change the Description of the Active Advanced Condition	You can change the description of the active advanced condition by modifying the text displayed in the Description field. Click Save to store the changed description.
   Execute the Active Advanced Condition	Click Execute to generate a case series by using the active advanced condition.

   
   

Note that modifying the field values in the Advanced Conditions Editor for an active advanced condition or saving the active advanced condition by a different name changes the active advanced condition to reflect the most recent changes. The active advanced condition also changes in case you modify the field values in the Advanced Conditions Editor and execute the advanced condition without saving the modifications.

5.3 Working with Saved Advanced Conditions

The ADVANCED CONDITIONS LIBRARY page lists all the advanced conditions saved to the system.

To access this page, navigate to Queries > Advanced Conditions > Library.

The ADVANCED CONDITIONS LIBRARY page appears with the following information about each saved advanced condition:

• Name of the advanced condition.

• Description of the advanced condition.

• Date when the advanced condition was last modified.

• Full name of the user who created the advanced condition.

• Category assigned to the advanced condition.

• Data source (Insight Mart/Argus Mart) of the advanced condition.

• Query type and Date (Current/Historical) for Argus Mart data source only.