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:

Total 1993600501
Product A2200 
Product B 1400 
Total 2203600580
Product A2200 
Product B 1400 


But I end up getting this

Total 11983600402
Product A992200101
Product B991400301
Total 2403600560
Product A202200180
Product B201400380


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

  • jaeW_at_Onyx
    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.

    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"


  • 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!**