Multiple Queries Part 2

Introduction

This Guide follows the Multiple Queries Part 1 Guide. If you have not already, please read through that guide or watch the video before starting this one.

Part 2 of the Multiple Queries process involves telling InfoView how to match up the rows in your two result tables.

This happens in two steps:

  • Merging: The columns in query one and two that have the overlapping content must be merged together.
  • Detail variables: For each object in one query that you want to use with objects from the other query, you have to make a detail variable and tie it to your merged object.

By merging two columns into one and tying other objects to the merged object, you can build the required bridge between your two tables. 

Video

Part 2: Merging Tables

Before we start merging our result tables together, note how the objects from the two queries behave.

All of the objects initially display in a complete list alphabetically.

Screenshot of InfoView Available Objects for multiple query example

However, when I click on an object from one query, the objects from the other query will gray out. If I try to add an object from one query to a table containing objects from the other query, "dropping" the object will have no effect.

Screenshot of InfoView Available Objects for multiple query example with one field selected

For ease of reading, you can also organize your fields by query via the button at the bottom of the Available Objects Data Palette:

Screenshot of InfoView Available Objects for multiple query example showing objects by query

Step 1: Merge Your Objects-In-Common

1. Select the two columns that contain the same information: click on one and holding down the control button and clicking on the second. Even though the object is grayed out, it is still selectable with this method.

Example: Class Buildings

CS Bldg Code and B Building Name contain the same names, so I'll select both objects.

Screenshot of InfoView selecting two items to be merged

2. In the Menu Data Access, in the submenu Data Objects, click the "Merge" button.

Screenshot of InfoView showing the location of the merge button in the menus

Note: you can also right-click (command + click on a mac) and select “Merge” from the menu.

You'll notice that the two objects have been grouped under a single name with an expandable plus sign next to it. Underneath are the two (or more) original fields.

Screenshot of InfoView showing the merged objects

All of the fields, including both original fields and the merged object, can be added to any table with any composition of objects from either query.

If the two merged fields do not contain exactly the same information (which is typical), each of the three objects will show slightly different information.

Example: Class Buildings

CS Bldg Code from the Course Review universe has slightly different content than the field B Building Name from the Facilities universe:

  • B Building Name has some buildings not contained in CS Bldg Code
    • Not all buildings on campus are used for courses, so it makes sense that B Building Name contains a much longer list. 
  • CS Bldg Code has a few buildings not contained in B Building Name
    • A few class locations that are not counted as buildings in the Facilities universe, such as "50 MTR POOL"
  • The Merged Object will compile a list of distinct values from both CS Bldg Code and B Building Name.
    • This is the longest list.

3. Complete as many merges as you need.

Step 2: Create Detail Variables

So far, merging your objects will only help you add those particular objects to tables from both queries. What about other objects?

Those fields can be made more flexible by making detail variables and tying them back to the merged object. Detail variables with this method essentially tell InfoView that anywhere where the merged object can be used, the detail object can be used too. Because the merged object is automatically compatible with all of the objects in your two queries, the detail object will also attain this attribute.

1. Create a new Variable.

Need a refresher? Check out our Create a Variable Guide.

2. Name your variable something unique. (See below for screenshot)

Tip: I usually name my detail variables very similar to the field that I want to mimic, e.g. "AGE DETAIL"

3. Change the "Qualification" to "Detail"

4. In the new field "Associated Dimension" pick your merged object (the original merged objects work here too).

5. In the formula, make the variable exactly equal to the field that you want to add.

Syntax: =[Object_name]

Example: Class Buildings

Screenshot of InfoView create a detail variable

6. Click OK. You can now add the detail variable to any table and it will contain exactly the same information as the original field.

Example: Class Buildings

Screenshot of InfoView showing columns from the first query in the same table as a detail object from the second query

Tips & Tricks: Minimizing Work

Making detail objects is a pain, so only make detail objects for fields that you actually need. Another trick is to minimize the number of detail objects that you need to make by comparing your result tables and determine which one has fewer columns.

Example: Class Buildings

The second query based off of the Facilities universe has only three result objects whereas the first query has 11. One column in the Facilities query has been merged, so I would only need to make two detail objects, one for each remaining column in query two, to make sure that every object works with every other object. If I made detail objects for columns in the first query, I'd have to make 10!

Troubleshooting

Because Multiple Queries of this type are completed so manually, they are prone to more errors and difficulties.

One very typical problem is the #MULTIVALUE error.

This error occurs when you add a detail object to a table with fields from the other query. If your detail object contains more rows for any record where the other columns have just one row, then your detail object will display the multivalue error.

A multivalue error simply means that InfoView wants to display one single value, but has more than one to choose from and does not know which one it should show.

Example: Class Buildings

Oddly, “PORTER ACAD” has two different current ages listed in the Facilities universe:

Screenshot of multiple rows in facilities universe

Given the above, if we add the Age Detail to a table containing values from the course report that we wrote, we will receive a #MULTIVALUE because InfoView does not know which of the ages to display:

Screenshot of InfoView Multivalue error

Solution

It is possible to force InfoView to show all of the rows, but this may drastically increase your row count

Here’s how to force InfoView to show all values:

1. To get to the “Format Table” menu, hover your mouse over the edge of your table until your cursor has the cross-hatch arrow form:

 Screenshot of InfoView cross-hatch-arrow cursor

2. Then, complete one of the following:

  • Right-click (control+ click on a mac) on the very outside edge of your table and select the last option “Format Table”
  • Click once normally and type in Ctrl + D
  • Click once normally and navigate to the Formatting Menu, then the Tools submenu and select the middle button:

Screenshot of InfoView button location

3. In the “Format Table” menu under “General”, check the box “Avoid duplicate row aggregation”:

Screenshot of InfoView Format Table option

4. Click OK.

If you have any other difficulties, consider attending one of our Open Labs. If you need help immediately, feel free to give us a call or email and be prepared to send us your report!