Summary dataset (from detailed dataset)

John-Peddle

We have a dataset (agent hours) that contains cumulative/summary data by status/day, and a beast mode to determine the total number of hours worked (something along the lines of start time - non work activities/statuses - end time = total actual hours worked), plus another dataset that is detailed/transactional for commissions paid on every offer# provided by an Offer Agent that was ultimately accepted & completed. 

 

It's unclear if the summary dataset we have was imported that way, or if a transform of sorts was done, and I've nothing comes back in Domo searches to locate information on HOW to create a summary dataset from a detailed one, or if it's even possible; hoping to get some assistance or pointed in the right direction on how we can accomplish this, preferably using ETL since my SQL understanding would not be adequate.

 

To summarize (no pun intended!), we want to take a detailed dataset and make it into a summarized dataset of commissions paid, by agent, by day in Domo so that we can join ith with an existing summary dataset that provides the agents total hours worked that day in order to arrive at an "hourly rate" based on commissions paid/hours worked. 

 

Hope that makes sense, and thanks in advance for any assistance you can provide!

Property_Ninja

Hi John,

 

In ETL you can input your detailed dataset, then your next step would be a "Group By" under "EDIT COLUMNS" where you select what columns identify the grouping (agent and date), then name your new column in step 2, choose which column you would like to aggregate (commissions paid), then lastly, choose how to aggregate it (most likely sum). Create your output dataset with the aggregation. Run the ETL flow so it creates your new aggregated dataset.

 

Go back into the same ETL flow and start a new step where you choose two input data sets, the one you just created and the agent hours data set. Under "COMBINE DATA" choose "Join Data" and connect the two data sets on the identifying columns (agent and date). Then create your final dataset, which will have commissions paid and agent hours joined.

 

The whole flow will look something like the attached image.

 

Hope this helps,

-Brian

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

Property_Ninja

Hi John,

 

In ETL you can input your detailed dataset, then your next step would be a "Group By" under "EDIT COLUMNS" where you select what columns identify the grouping (agent and date), then name your new column in step 2, choose which column you would like to aggregate (commissions paid), then lastly, choose how to aggregate it (most likely sum). Create your output dataset with the aggregation. Run the ETL flow so it creates your new aggregated dataset.

 

Go back into the same ETL flow and start a new step where you choose two input data sets, the one you just created and the agent hours data set. Under "COMBINE DATA" choose "Join Data" and connect the two data sets on the identifying columns (agent and date). Then create your final dataset, which will have commissions paid and agent hours joined.

 

The whole flow will look something like the attached image.

 

Hope this helps,

-Brian

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

John-Peddle

@Property_Ninja: hey Brian, thanks for the reply!

 

Finally found a link with useful information in Domo University but needed to change search terms to locate it - it's a breakdown of what each action in ETL can do, some with a quick video tutorial, and the one for GROUP BY seemed like what we wanted, as you've provided in your example!

 

Our first attempt was slightly different than the example you provided, Brian, but now we have something else to work from if our first attempt doesn't work as hoped!

 

 

And for anyone else looking to do something like this and/or unsure of the process, this may help in addition to Brian's example: https://knowledge.domo.com/Prepare/Magic_Transforms/ETL_DataFlows/02ETL_Actions%3A_Edit_Columns

 

Thanks again, @Property_Ninja, your assistance and example are greatly appreciated!