Mix, match, and mash-up: Become a data DJ with Analytics Plus

When you decide to build complex reports that involve data from different departments (example- an ROI report involving sales and marketing), you would quickly realize that you have to get the data you need from different tables, and blend it together to get the metrics you need to see - sort of like a DJ blending together different songs with similar key notes to product one fantastic track.

With Analytics Plus, you have a couple of options to combine data from two or more tables within a given database. You can

1. Auto join tables by establishing a lookup relationship

This option is useful when you have common columns between the tables you are trying to link that have unique values (more on unique values later). This is the recommend option for beginners.

2. Create a query table using a SQL JOIN or UNION

This option is useful when you have common columns between the tables, but don't have unique values.

Now let's go ahead and explore how each of these options work.

Auto joining tables by creating a lookup column

Two or more tables in a reporting database can be joined using the auto join feature, by creating a lookup column. For this to work, the tables need to have a common column, with unique values amongst them. Let's consider the below Store sales database as an example. It has 3 tables,

Sales - This table contains the amount of product that is sold
Sales person - The details of the sales person who sold the products
Product - The product that is sold

As you can see, both Sales and the Sales Person tables have a common column Sales Person ID. The column Sales Person ID is unique to each sales person i.e, no two sales persons can have the same Sales Person ID. Since the values in the Sales Person ID column is unique, it is a classic case to use the Lookup Column functionality to link both the tables.

Likewise, the Sales and Product tables have a common column, Product ID, that can be used to establish a lookup relationship between the two tables.

Once you've joined two or more tables using lookup, Analytics Plus will automatically detect the relationship between the tables while creating reports and promptly fetch data from all the related tables to generate the report.

To create a lookup relationship between two tables, (These steps are explained using our original example Sales and Sales Person tables for better understandability)

1. Go to the Store Sales Database.

2. From the explorer bar, open the Sales table.

3. Right-click on Sales Person ID and select Change to Lookup Column.

4. In the dialog box that opens, select Sales Person ID from Sales Person table.

5. You've now established a relationship between the two tables.

6. Create a report using either one of the tables. Fields from the second table will also be available to be used in reports.

Here's a quick GIF demo for the above steps

Query table

The second option to blend data from different tables is by using an SQL JOIN or UNION query to create a query table. This query table can then be used to create reports and dashboards for analysis.

To create a SQL table,

1. Click on the New button in the explorer bar and select New Table.

2. Now click on Query Table from the list of options and proceed to create your query table using an SQL Select query.

Here's a sample SQL query to combine data from Sales and Sales Person tables, using the common column 'Sales Person ID'

SELECT "Date", "Product ID", "Sales Person ID", "Sales" FROM "Sales" JOIN "Sales Person" ON "Sales Person"."Sales Person ID" = "Sales"."Sales Person ID"