We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Combining aggregated and non-aggregated rows

arnettmh
7 - Meteor

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 IDCust NameAssigned RegionAssigned Region OrdersWeb RegisteredWebsite Registration DateMonth-YearBusiness NameSite Visit Count
3000423JENNIFER SMITHSouth5No  Jen's Designs 
3001645VIN DELINOWest7No  VinCo 
3002323PAT MOORECentral204Yes1/7/20222022_01Consultants Weymouth1
3002323PAT MOORECentral204Yes1/7/20222022_02Consultants Weymouth2
3002323PAT MOORECentral204Yes1/7/20222022_03Consultants Weymouth2
3002323PAT MOORECentral204Yes1/7/20222022_05Consultants Weymouth2
3002323PAT MOORECentral204Yes1/7/20222022_06Consultants Weymouth1
4 REPLIES 4
NataliaElias
7 - Meteor

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

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

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

arnettmh
7 - Meteor

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!

Labels