Table from Multiple Datasets

Shumilex

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)

 

GrantSmith

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.

MarkSnodgrass

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. 

jaeW_at_Onyx

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