Minus Queries

Introduction

Note: For simplicity, this Guide assumes that you are making a report with two queries, but InfoView supports many more than just two.

Diagram showing how minus queries work

Minus queries ask InfoView to complete two queries, compare the results and present the result rows of the first query minus the result rows of the second query. These work very similarly to Union Queries, but instead of adding, the results are subtracted. Because this method is using subtraction, the order of your queries is important.

Like Union Queries, Minus Queries must contain the same number of Result Objects and the order of the Result Objects matters. InfoView will be comparing the results of both queries in order to perform the subtraction. If the result rows of the second query are drastically different from the first, for example because the columns are out of order, no rows will be removed from the total results and your results will be the same as running the first query by itself.

Remember: the results of your second query will never be shown.

Video

Minus Queries

Example: Fictitious Graduate Students by Major, Registered by End of Term but not Third Week

This report looks at fictitious graduate students registered in a major by the end of the term, but who had not registered by the third week of the same term.

The first query will return those students registered in the End of Term data extract. The second query will return those students registered in the 3rd week extract. The minus query will remove those names that match between them and leave only the names of students who had registered between the third week and the end of term extracts.

Note: For privacy reasons, the data used in this example is not real.

Building a Minus Query

1. Start a new report.

2. Write your first query: select your result columns and define your filters.

Example: Fictitious Grad Students

Screenshot of InfoView example query

3. Click on the "Add a Combined Query" button in the top row of the query panel.

Screenshot of InfoView showing the location of the combined query button

Note how a box pops in below your list of objects and that "union" is selected by default. The two Combined queries are your two queries and you can toggle between them by clicking on the names.

Screenshot of InfoView showing additional combined query box in the query panel

Note also that Combined query 1 and Combined query 2 both have exactly the same Result Objects selected by default, but that the filters are different.

4. Double click on the word "Union" twice to change it to "minus".

Screenshot of InfoView query panel showing a union query and where to click to make it a minus query

5. Complete your second query: the result columns from your first query are pre-selected but can be replaced with others, including empty filler cells. Replacing the columns is not recommended because the comparison of your results queries will be looking for exact matches.

Example: Fictitious Grad Students

The result objects for the second query are exactly the same as for the first.

6. Complete your second query: define your filters.

Note: If both of you queries are filtering on the same value via a prompt, you'll need to be very careful. Both queries need to have a filter with exact same prompt text (the value that appears in the text box) in order to unify into one single prompt, otherwise the field will present two separate prompts in the prompt window.

Example: Fictitious Grad Students

The filters on the second query are exactly the same as for the first query, except that the extract is different.

Screenshot of InfoView example minus query 2

7. Run your report.

Example: Fictitious Graduate Students by Major, Registered by End of Term but not Third Week

Query 1 (End of Term) results as a single query:

Screenshot of InfoView example query results for end of term

Query 2 (3rd Week) results as a single query:

Screenshot of InfoView example query third week results only

Query 1 results minus query 2 results:

Screenshot of InfoView example minus query results (end of term minus third week)

Tips and Tricks: Nesting Unions, Minus and Intersection Queries

Union, Minus and Intersection queries can be nested in the same way that filters can be nested with ANDs and ORs (see the Nesting Guide for more information.)

The button for nesting is in the top right corner of the combined query box:

Screenshot of InfoView combined query nesting button location