Combine 2 datasets with join or append while preventing duplicates

I have 2 datasets:
- 1 containing conversions per day per product
- 1 containing the costs per day (in total)
I am trying to get to this:
Costs | Sales | Revenue | ROI | |
Total 1 | 99 | 3 | 600 | 501 |
Product A | 2 | 200 | ||
Product B | 1 | 400 | ||
Total 2 | 20 | 3 | 600 | 580 |
Product A | 2 | 200 | ||
Product B | 1 | 400 |
But I end up getting this
Costs | Sales | Revenue | ROI | |
Total 1 | 198 | 3 | 600 | 402 |
Product A | 99 | 2 | 200 | 101 |
Product B | 99 | 1 | 400 | 301 |
Total 2 | 40 | 3 | 600 | 560 |
Product A | 20 | 2 | 200 | 180 |
Product B | 20 | 1 | 400 | 380 |
As you can see I get the wrong calculations do to Domo forcing every field to have the same cost.
Total 1 & Total 2 are 2 different companies to make it even more difficult.
Is there any way to prevent this or is this just the nature Domo works?
Thanks in advance
Best Answer
-
In Domo you'll usually want to APPEND / UNION your data together not JOIN when you're combining transactions.
Sales and Costs are a type of transaction. Alternatively you can think of Individual activity vs. Daily Total as different types of transactions. Either way, the answer is the same. UNION your data instead of APPEND. Use a constant Activity Type = Sale or Activity Type = Cost to differentiate btwn the type of row you're looking at.
then as you construct your beast mode you can use a CASE statement to aggregate the correct subset of data.
Conceptually it's the same problem as the use case covered in this tutorial. https://www.youtube.com/watch?v=PVbOeLSae9o
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Answers
-
Hi @user046467
This isn't necessarily a Domo issue but rather a data model / structure issue. You're trying to combine two different datasets with different slices (one by product & day, one by just day). You'd need to restructure your data so they're speaking the same language (in your case have both be by product & day). In other terms you have a one (day) to many (products) relationship which is causing your data to be duplicated when you join it together.
Restructure your data so you can join based on Day AND Product instead of just the day and that'd solve your duplication issue.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
In Domo you'll usually want to APPEND / UNION your data together not JOIN when you're combining transactions.
Sales and Costs are a type of transaction. Alternatively you can think of Individual activity vs. Daily Total as different types of transactions. Either way, the answer is the same. UNION your data instead of APPEND. Use a constant Activity Type = Sale or Activity Type = Cost to differentiate btwn the type of row you're looking at.
then as you construct your beast mode you can use a CASE statement to aggregate the correct subset of data.
Conceptually it's the same problem as the use case covered in this tutorial. https://www.youtube.com/watch?v=PVbOeLSae9o
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 305 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3K Transform
- 106 SQL DataFlows
- 645 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 774 Beast Mode
- 74 App Studio
- 43 Variables
- 728 Automate
- 186 Apps
- 467 APIs & Domo Developer
- 61 Workflows
- 14 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 403 Distribute
- 117 Domo Everywhere
- 277 Scheduled Reports
- 9 Software Integrations
- 136 Manage
- 133 Governance & Security
- 8 Domo Community Gallery
- 44 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 112 Community Announcements
- 4.8K Archive