Aggregation Solution for attached Use Case

[timewaste]

Hello,

Can anyone please help me design following attached use case in a workflow requiring aggregation? 

Thanks a ton!

[DanM]

Hi @timewaste,

Thank you for your contribution to the Community! The Alteryx Community is here to assist you with questions, issues and ideas. We ask that you attempt to build the workflow on your own as a first step. If you encounter specific issues or errors along the way, the Alteryx Community is here to help!

We have many resources to assist you including:

• Sample workflows found under the Help in Designer
• Help found by selecting the ? in Designer

Thanks

DanM

[Joe_Mako · Accepted solution]

From your description, the goal as I understand it is you want to take two tables, MIG_ID and WCID, and evenly distribute the totals from the MIG_ID table into the WCIDs. The part that is not so clear is the remainders, because your totals are not integers, the normal definition of reminder does not apply. I assume you want the final WCID level Count to ideally be the largest integer that divides into the Total n times (where n is the count of WCIDs), and any left over goes into the first WCID's Count.

Attached is my attempt.

- for the MIG_ID table, Transpose, putting each field name into a field called "Name" and total into a field named "Value"
- Filter out records where Value is zero, keeping only non-zero records
- for the WCID table, Formula to create the "Name" field:

IF [BU2_FLAG]=1 THEN "BU2_FL_TOTAL" ELSE [BU]+"_TOTAL" ENDIF

- Summarize, group by Name, and Count the records with "Count of WCs"
- Join the Count with the MIG info on Name
- Formula to calculate the "Count" and "Remainder":

Count:

FLOOR([Value]/[Count of WCs])

The largest integer that divides into the Total n times (where n is the count of WCIDs)

Remainder:

[Value]-([Count]*[Count of WCs])

The left over after multiplying that largest integer by the count of WCIDs and subtracting that from the total

- Join with the WCID data before aggregation on Name
- Unique tool to split the data into two streams, the U stream will only have the first record per MIG_ID and Name combination
- to the U stream, add the Remainder to the Count
- Union the streams together
- Select tool to drop the Remainder field

I added a few of more tools in the container to validate that aggregation of the Count field matches the totals from the MIG_ID table

[timewaste]

@Joe_Mako Thanks a ton!