Sometimes you need to merge data from two separate queries. This guide offers examples and best practices for merging data in the Reporting module. 

Note: This guide assumes readers have a working knowledge of generating queries in Reporting. Refer to this article to get acquainted with Reporting and creating queries.

Primary Query

When you combine queries, you begin with an initial query from one data exploration and combine another dataset to this primary source. The first query is the primary query by default, though you may reset and rename primary queries post-merge.

Merging requires at least one common variable upon which to match datasets, ideally a unique identifier, like students' State ID. A unique identifier is critical because students sometimes have the same name and spelling.

Create your primary query, using filters to set parameters on the data displayed, and ensure you've included a key variable for merging.

In this example, I'm including Teacher and School Name from the Courses data table in my primary query, filtering on the current academic year. I'm ready to merge this query, so I will select the gearbox in the upper right corner, and "Merge results":

Choose an Explore - Secondary Query

You'll be prompted to Choose and Explore before you can build your secondary query. In this example, I'm sticking with Student Level Data:

Once you choose an explore, you'll see the familiar default reporting window and may begin creating your second query. Again, include a variable that can be matched back to the primary query. In this example, I'm including the variables Communications User and Communications Count in my report and filtering on Communications School Year = 2023. 

Next, click 'Save' in the upper right corner of your Merge Query. Your screen will refresh with the merged data. Merge rules will appear at the top, and any variables that are exact matches in the files will automatically appear in the merge rules. In my example, I'm marrying data by Teacher Name and User, variables that aren't immediately recognizable as a match; consequently, I'm seeing an error message under Merge Rules:

No problem. I'll have to provide the information directly by selecting 'Add merge rule for Student Level Data 2'.

As soon as I select this option, the Merge Rules intuit that I want to merge Student Level Data Primary using the variable Courses Teacher Name to Student Level Data 2 using the variable Communications User:

Once the merge rules are set, click Run in the upper right corner to see the merged results. Observations from the secondary source will be dropped if those records cannot be matched to the primary source file. Primary source records that don't have a match in the second query will remain in the report but will have null values in the columns associated with the secondary query.

Now you can create calculations on the merged data, adjust visualization settings, and save the results to a dashboard for additional analysis or downloading. 

Happy analyzing!