Creating a Dataset based on UNION of tables using custom SQL query

Use-case:

I have 2 tables: car_sales and tv_sales. Each table has a “sales amount” column and a product name column (i.e. tv_sales.name, tv_sales.sales_amount and car_sales.prod_name, car_sales.salesAmt). I’ve created 2 datasets in Arcadia, one for tv_sales, one for car_sales. I want to create a new dataset called “Total Sales” that includes data from both tv and car sales that will allow me to analyze both sales sets of sales data together. For example, I want to show total sales across all categories and a bar chart showing total sales for each category.

Solution: Use the SQL Visual and “Dataset directly from query” feature to create an entire new dataset based on a custom SQL query.

Steps:

Step 0. Choose an existing Dataset based on the 1 of the base tables (i.e. tv_sales), and click “Create Dashboard”. Once inside the Dashboard, Edit the initial visual and choose “SQL Visual” type.

Step 1. Create custom UNION SQL query through SQL Visual.

NOTE: The extra column called “base_table” leaves an indicator of the original table name tied to each respective row of data. This is not required, but might be useful when checking later to make sure that all of the records from each table were merged together properly into the new dataset properly.

Step 2: Click “Save Table”, select “Dataset directly from Query”, and provide a name for your new unioned Dataset (This can be modified later).

Step 3. Go to the Data tab, search for and click on the new Dataset (i.e. Union Sales Data), and create a new Dashboard/Visual to confirm that your 2 tables have been unioned together. For example, you can do a record count against the “base_table” dimension field to check whether the number of records between both of your tables in the new dataset matches each of your base tables record counts before the union.

1 Like