Overview
In this example, you use two different systems of the same type and you would like to get a unified number or data set around them. To keep track of their everyday tasks:
The Marketing team is using Basecamp
The HR team is using Asana.
You want to have a single insight that helps you track the progress from one place.
Approach 1: Merge the tasks from the insight
When you want to merge data from the two systems, you can use the following SQL query in the insight:
SELECT SUM(tasks) totalTasks
FROM
(
SELECT COUNT(*) as tasks FROM asana_tasks
UNION ALL
SELECT COUNT(*) as tasks FROM basecamp_todos
)
If you need to apply this logic across multiple insights, for each one of them you have to apply the UNION. If any of the entities change or you incorporate another task management system, you have to go over all the insights and apply the changes.
Approach 2: Merge the tasks with a virtual data source
By creating a virtual data source, you can control the changes from a single place.
Perform the following:
Create a virtual data source for the tasks.
Define its key asall_tasks.
For more information, see Create virtual data sources.In the SQL editor, enter the same SQL query as in the first approach.
This way, your virtual data source combines data from the two systems. You can reuseall_tasksin your insights.In the SQL of the insights, use the following:
SELECT * FROM all_tasks
For more information, see Create insights with SQL.