Table from Multiple Datasets

Hi, 

 

I want to create a table that will pull data from multiple datasets. For example: each dataset has user name and data fields. 

 

1st dataset has: username, date, ticket type, count by type.

2nd dataset has: username, date, inbound calls, outbound calls, productivity.

3rd dataset has: username, date, net collected, potential revenue.

 

I want to create a dataset that has username, date, count by ticket type, total inbound calls, total outbound calls, total productivity, total net collected, total potential revenue. (So these would be the headers and then each agent would appear once with their total for each column.)

 

I included a picture of sort of what I want to achieve... (headers are different in this example)

 

Domo Dojo.jpg

Comments

  • Hi @Shumilex 

     

    You can utilize a Magic ETL 2.0 data flow with 3 different input data sources, then join the data sets together using a Join tile bsaed on the date and username fields. The resulting dataset would have all of the columns in the same dataset.

     

    Alternatively you could use a Dataset View (in beta, talk with your CSM to get it enabled) to do the joining.

     

    The caveat to this would be if each dataset is guaranteed to have every date and username then you could use an inner join which would be faster otherwise you might want to look into a FULL OUTER join which will include records from both data sets in the join if a record isn't found in one of them.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • To add to @GrantSmith 's solution, you may want to use some group by tiles along with the join tiles to get the totals you are looking for (it's unclear to me if data was already aggregated). Here's a screenshot of what that would look like. groupbyjoinexample.PNG

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • better solution would be to avoid doing a GROUP BY in ETL alltogether and just APPEND the data.  (make sure to align columns with common field names).

     

    then in analyzer you can use beast modes to just SUM() or COUNT() the metrics of interest.

     

    the proposed solution is superior b/c it allows you to filter on all the columns available in your dataset without rewriting your ETL.

     

    if you apply GROUP BYs in Magic, then you can only filter on columns that are included in the GROUP BY clause.  This means if someone says "hey i want to see these metrics for just the San Francisco office.", you would have to refactor your ETL or produce a new dataset...

    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"
This discussion has been closed.