Creating a Dataset
Hi All,
I have been testing/trialling a lot of Business Intelligence and Domo is the only BI that has instantly clicked with me. I think this is the BI which would automate a lot of work and provide smarter reports.
I want to know what would be the best practice to join multiple sales reports - these needs to formatted. We get reports from the marektplace in spreadsheet format and I convert into Google Sheets. We will use Google Sheets as connection. Each reports have different columns and want to combine into a one data set. To create a consolidated sales figures.
From each reports, I will need below columns. I will have to create and calcuate some of the columns. Do I upload raw datas then use ETL to format each reports in below columns, and then join each sales reports using appended row?
Date
Fiscal Week
Channel
EAN Code
Style
Season
Category
Gross Sales
Returns Sales
Net Sales
Sales Units
Return Units
Reported Sales
Unit Cost
Cost of Sales
Best Answer
-
Hi @user021892 - Welcome to the Dojo!
Your data sources and your use case will determine how you want to combine your datasets together.
Lets say for simplicity sake you have two different datasets with the following data:
Sales:
Date Sale Quantity 1/1/2020 5000 100 2/1/2020 7500 150 3/1/2020 2500 50 Revenue:
Date Revenue 1/1/2020 1000 2/1/2020 1250 3/1/2020 250 4/1/2020 1000000000 If you utilize an APPEND the two datasets in Magic ETL it will automatically determine the different columns and allow your resulting dataset to include all columns (you can change it to only include columns found in one of the datasets). The resulting dataset would look like:
Date Amount Quantity Revenue 1/1/2020 5000 100 2/1/2020 7500 150 3/1/2020 2500 50 1/1/2020 1000 2/1/2020 1250 3/1/2020 250 4/1/2020 1000000000 If you utilize a JOIN instead it will combine the data width wise where the column you're joining on (in this date Date) match across the two tables (This is considered an INNER JOIN, there are also LEFT JOIN and RIGHT JOINs which will return all data in the left table or right table respectively irregardless if there's a matching record in the opposite table.)
For more information here's a link to a tutorial describing the different SQL JOIN types: https://www.w3schools.com/sql/sql_join.asp
JOIN dataset:
Date Amount Quantity Revenue 1/1/2020 5000 100 1000 2/1/2020 7500 150 1250 3/1/2020 2500 50 250 (4/1/2020 is missing because it doesn't exist in both tables and we're doing an INNER JOIN)
Again, this depends on the structure of your data and the resulting format you're wanting the data to be in but in summary:
APPEND (UNION) = High
JOIN = Wide
If you have the same columns (and importantly - they represent the same data) then you'd likely want to utilize an append.
As a side note you might want to look into the large selection of data connectors that Domo offers to see if you can utilize one of those to pull your marketplace data instead of having to manually convert it to a Google Sheet.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers
-
Hi @user021892 - Welcome to the Dojo!
Your data sources and your use case will determine how you want to combine your datasets together.
Lets say for simplicity sake you have two different datasets with the following data:
Sales:
Date Sale Quantity 1/1/2020 5000 100 2/1/2020 7500 150 3/1/2020 2500 50 Revenue:
Date Revenue 1/1/2020 1000 2/1/2020 1250 3/1/2020 250 4/1/2020 1000000000 If you utilize an APPEND the two datasets in Magic ETL it will automatically determine the different columns and allow your resulting dataset to include all columns (you can change it to only include columns found in one of the datasets). The resulting dataset would look like:
Date Amount Quantity Revenue 1/1/2020 5000 100 2/1/2020 7500 150 3/1/2020 2500 50 1/1/2020 1000 2/1/2020 1250 3/1/2020 250 4/1/2020 1000000000 If you utilize a JOIN instead it will combine the data width wise where the column you're joining on (in this date Date) match across the two tables (This is considered an INNER JOIN, there are also LEFT JOIN and RIGHT JOINs which will return all data in the left table or right table respectively irregardless if there's a matching record in the opposite table.)
For more information here's a link to a tutorial describing the different SQL JOIN types: https://www.w3schools.com/sql/sql_join.asp
JOIN dataset:
Date Amount Quantity Revenue 1/1/2020 5000 100 1000 2/1/2020 7500 150 1250 3/1/2020 2500 50 250 (4/1/2020 is missing because it doesn't exist in both tables and we're doing an INNER JOIN)
Again, this depends on the structure of your data and the resulting format you're wanting the data to be in but in summary:
APPEND (UNION) = High
JOIN = Wide
If you have the same columns (and importantly - they represent the same data) then you'd likely want to utilize an append.
As a side note you might want to look into the large selection of data connectors that Domo offers to see if you can utilize one of those to pull your marketplace data instead of having to manually convert it to a Google Sheet.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Thank you for the detailed explanation. I have managed to create a neat combined data file. But still think it could be improved. See below a picture of the lineage.
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 60 App Studio
- 41 Variables
- 688 Automate
- 177 Apps
- 453 APIs & Domo Developer
- 48 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 127 Manage
- 124 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive