Domo totals are way higher than they should be
My Domo totals are way higher than then should be when I sum them, for example plays total should be 3,075 and it's over 48,000. When I try using the sum distinct, the values are much lower than they should be. Additionally, when I use the sum distinct I'm getting duplicates.
Does anyone have ideas of how to fix these errors in the ETL or Beast mode?
Comments
-
Hi @user031517
A little more information about how your data is structured would help or some sample anonymized data would be great too. Without knowing how your data is structures it's difficult to pinpoint the problem.
How is the data being populated? Is it being done via a join? What does a single row in your dataset represent?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
I've merged two data sets with ETL edits to a right join. I am including an anonymized version of how it looks and the ETL.
Below are the issues of how it looks with the sum and how it looks with distinct sum:0 -
I would bet a year's salary that the problem is in your JOIN clause.
There are two possibilities,
1) you're JOINING on a column that sometimes contains NULL.
You should never JOIN on a NULL because it creates a cartesian product. in other words, you ultimately multiple each row on both sides of the JOIN by any row that contains a NULL.
2) you have JOIN criteria that are not unique. (i.e. on the LEFT my JOIN criteria has two rows containing 'A' and on the right i have 6 rows containing 'A' I'll end up with 12 rows.)
If your JOIN criteria were correct you would not need Remove Duplicates.
To solve this problem.
1) ensure that you are not JOINing on columns that contain NULL. if in doubt replace NULL with an empty string.
2) to test my theory, put a GROUP BY on the smaller table and group by the JOIN columns. Your Row count will go down to expected values.
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"1 -
@jaeW_at_Onyx Yes, those seem to both be it, I can fix the null for a empty string. I have to join on criteria that are not unique, i.e. joining on a station name and song name and thus they exist in both columns, how can I avoid that? Just with removing the duplicates. THANK YOU!
0
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
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 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