What is a Join or a Union in Virtuous BI?

There may be times when you're building a custom Virtuous BI report that you need to combine different data points to create a single Visualization. In this article we'll dive into the two ways you can combine data in Virtuous BI with a Join or a Union.

What is a Join?

When using Virtuous BI there will be times when you want to combine differing tables like email statistics and contact records. These two data sets don't have much in common so you will need to use a Join. A join is when you take two differing data sets and place them side by side. Essentially, if you think of tables like data in a spreadsheet, we are just adding columns to the table.

In order to join two tables, you'll first need to add the two tables to the report (check out this article on the basics of building a Virtuous BI Report). 

Once your tables are in the report, you'll then either duplicate a table to create this as a completely different table (which you can do by hovering over a table, selecting the 3 dots, then selecting Duplicate) or join the two tables together into one of the existing tables. Either way, once you're ready to join, you'll hover over the table, select the three dots in the menu, choose Element Source, then Join.

Once you've selected join, you'll need to choose the other table that you wish to join with the table you started with in the last step. After choosing the other table you'll be brought to a screen where you'll have a few options with how to bring this data together.

Your first choice is to determine how you will join the data:

• Left Outer Join - Brings in all of the data from the table on the LEFT. Only brings in the data from the table on the RIGHT if it has a match with data on the left.
• Inner Join - Only brings in the data if there is a match to BOTH data sets.
• Right Outer Join - Brings in all of the data from the table on the RIGHT. Only brings in the data from the table on the LEFT if it has a match with data on the left.
• Full Outer Join - Brings in ALL of the data from both data sets.

Your second choice in joining the data is to determine on what point is the data matching. In many other places in Virtuous you might use Contact ID - here, because of how the data is built, we always recommend using the DM ID for anything that has a Virtuous ID for best performance. If you have a situation where you need to combine on a Virtuous ID without a longer string ID, we recommend that you pair the ID with Organization ID.

Once you've chosen how your data will join, you have the opportunity to preview what that will look like. From the preview you will select Done, which will then create the new dataset in your Workbook.

What is a Union?

In the last section we looked a combining differing sets of data. There are times when you will want to combine similar types of data like pledges and gifts. This is where a Union will come in handy. A Union is when you take two similar data sets and stack them together. Essentially, if you think of tables like data in a spreadsheet, we are just adding rows to the table.

The process to create a Union is going to start very similar to a join. In order to Union two tables, you'll first need to add the two tables to the report (check out this article on the basics of building a Virtuous BI Report). 

Once your tables are in the report, you'll then either duplicate a table to create this as a completely different table (which you can do by hovering over a table, selecting the 3 dots, then selecting duplicate) or create a Union of the two tables together into one of the existing tables. Either way, once you're ready to create the union, you'll hover over the table, select the three dots in the menu, choose Element Source, then Union.

Once you've selected Union, you'll need to choose the other table that you wish to Union with the table you started with in the last step. After choosing the other table you'll be brought to a screen to show the comparison between the two data tables. Since we are adding rows in a union, we need to make sure the data lines up because they will all have the same header in that table. On this screen, you will see that Virtuous has already made matches in all the places where the exact same data exists in both tables. For the missing data sets you will need to either choose the appropriate column (like Gift ID = Pledge ID) in the second data set or leave it blank to have no data fill in those spaces for the second data set.

Once you've matched all the columns you can select Done. Virtuous will then create a new dataset of both of the tables.