Combining aggregated and non-aggregated rows

[arnettmh]

I am currently working on combining data from a few different datasets in Alteryx.  So far, everything has been doing well.  However, I need to combine data that lists total annual customer orders with data that is a count of times a customer has visited our website, broken down by month.

The total customer orders are already aggregated (summed) as a yearly total.  However, the monthly visit count is, of course, at the month-level (see example data below).  As you can see, for a customer who has visited the website multiple months within the year, the corresponding sales figured gets replicated based on the number of months the customer has visited the website.  Unfortunately, in my reporting, I need that monthly break-down of site visits, so I can't just summarize the totals monthly site visits.  How do I get the total annual orders to only be listed in a single row while keeping the monthly site visits?

Cust ID	Cust Name	Assigned Region	Assigned Region Orders	Web Registered	Website Registration Date	Month-Year	Business Name	Site Visit Count
3000423	JENNIFER SMITH	South	5	No			Jen's Designs
3001645	VIN DELINO	West	7	No			VinCo
3002323	PAT MOORE	Central	204	Yes	1/7/2022	2022_01	Consultants Weymouth	1
3002323	PAT MOORE	Central	204	Yes	1/7/2022	2022_02	Consultants Weymouth	2
3002323	PAT MOORE	Central	204	Yes	1/7/2022	2022_03	Consultants Weymouth	2
3002323	PAT MOORE	Central	204	Yes	1/7/2022	2022_05	Consultants Weymouth	2
3002323	PAT MOORE	Central	204	Yes	1/7/2022	2022_06	Consultants Weymouth	1

[NataliaElias]

Hi arnettmh, 

If I understand correctly your question I would do it like this:

1) Divide the database into two using the following condition: IsNull([Month-Year]) Take the False anchor. Do a summarize grouping by all fields except for [Website Registration Date], [Month-Year] and [Site Visit Count]. The goal for this is to create a 'total' row for the customers that have multiple rows, following the same format as the rows for the customers that do not have multiple rows. 

2) Union these 'new total rows' to your initial database (one new row per customer with multiple rows)

3) Use a formula tool with an if statement to replace [Assigned Region Orders] by blank when [Month-Year] is filled. 

Best, 

Natalia

[arnettmh]

Thanks for the response.  Perhaps I am doing something wrong, but when I union these two back together, I don't get a new "Total" row.  The Union output looks exactly the same as the initial data. Is there another step after I do the summarize GroupBy step?

[NataliaElias · Accepted solution]

Hi arnettmh, I have attached a workflow with your initial database following the steps I explained above. Let me know if this helps!

[arnettmh]

Thanks for the sample workflow.  This worked well!  Initially, I wasn't seeing the extra "Total" row added.  These extra rows were being added at the bottom of my dataset for some reason.  Once I added a Sort tool and sorted by the Customer ID, then it grouped them together as it should,

Thanks again for your help!