Guide to Merging Custom query based Data Sources in Bold BI

When working with Bold BI, you may find yourself needing to merge data from multiple data sources that have custom SQL enabled (Code mode). While Bold BI does not currently support direct merging of custom SQL data sources, there is a practical workaround that can be employed using SQL queries.

Workaround for Merging Custom query Data Sources

To merge data from different data sources (with Code mode), you can use the UNION or UNION ALL SQL operator. This allows you to combine the results of two or more SELECT statements into a single result set.

Sample Data:

Table name: sales1

Table name: sales2

Using UNION/UNION ALL Operator

Here’s a syntax of how you can use the UNION ALL / UNION operator to merge data from two tables:

SELECT * FROM table1
UNION
SELECT * FROM table2

Example

    SELECT
    	*
    FROM "public"."sales1" AS "sales1"
    UNION
    SELECT * from "public"."sales2"

Important Considerations

When using UNION or UNION ALL to merge data sources, ensure that:

• The number of columns in each SELECT statement is the same.
• The corresponding columns in each SELECT statement have the same data types.

Steps to Merge Data

1. Write custom SQL queries for each of your data sources.
2. Ensure that each query selects the same number of columns and that the columns have matching data types.
3. Use the UNION or UNION ALL operator to combine the queries.
4. Execute the combined query in Bold BI to retrieve a unified data set.

When to Use UNION vs. UNION ALL

• Use UNION to combine the results of the queries and eliminate duplicate rows.
• Use UNION ALL to combine the results of the queries while keeping all duplicate rows.

Additional References

• Merge Data Sources in Bold BI: Step-by-Step Guide
• Append data sources
• Joining tables