There may be times when you're building a custom Virtuous Analytics report that you need to combine different data points to create a single Chart. In this article we'll dive into the two ways you can combine data in Virtuous Analytics with a Join or a Union.
Table of Contents
What is a Join?
When using Virtuous Analytics 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 select the Data Element and then Table. Here, click the Join icon. Click here to learn more about the working with Tables and Pivot Tables in the Data Element.
Begin creating your Join by selecting the first Table you wish to include. Search all Tables or use current Workbook Elements. Selecting the Table allows you to preview the full dataset and available columns. Click the blue Select button when you're ready to move onto adding your second Table for your Join configuration.
Now, choose the second Table that you wish to Join with the first Table. Select the + button on the left-hand side of the screen to add another Source, your second Table. Analytics will recommend a few sources, but you're able to choose from any dataset or current Workbook Elements.
After choosing the second Table you'll need to configure the Join Type and Join Keys which determine how to bring this data together.
The Join Type will determine how you 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.
Next, the Join Keys determine on what point is the data matching between the two Tables. In many other places in Virtuous CRM+ 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 CRM+ ID for best performance. If you have a situation where you need to combine on a Virtuous CRM+ 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, select Done, which will then create the new dataset in your Workbook.
What is a Union?
While a Join typically combines differing sets of data, there may be 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: select the Data Element and then Table. Here, click the Union icon. Click here to learn more about the working with Tables and Pivot Tables in the Data Element.
Begin creating your Union by selecting the first Table you wish to include. Search all Tables or use current Workbook Elements. Selecting the Table allows you to preview the full dataset and available columns. Click the blue Select button when you're ready to move onto adding your second Table for your Union configuration.
Now, choose the second Table that you wish to Unite with the first Table. Select the + button next to Sources(1) on the left-hand side of the screen to add another Source, your second Table. You're able to choose from any dataset or current Workbook Elements.
After choosing the second Table, Analytics will display 2 columns showing the comparison between the two data tables. Since we are adding rows in a Union, we need to make sure the data lines up as they will all have the same column headers in that output. On this screen, you will see that Analytics 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. Analytics will then create a new dataset of uniting both of the Tables.
Joining or Uniting with an Existing Table
If your Workbook already has an existing Table in it that you'd like to Join and Unite with another Table with, rather than creating an entirely new Join or Union as demonstrated above, you can do so using the Element Source in the existing Table. To do so, hover over the Table, select the three dots in the menu, choose Element Source, then Join or Union.
Both Joins and Unions will ask you to select a source for your second Table to combine with this existing Table. Then, you will configure the Join or Union using the same steps as above to add columns or rows to your dataset.