Combine 2 datasets with join or append while preventing duplicates

user046467
user046467 Member
in Magic ETL

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:

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

 

But I end up getting this

 CostsSalesRevenueROI
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
    jaeW_at_Onyx Coach
    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"

Answers

  • GrantSmith
    GrantSmith Coach

    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!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    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"

Categories