Assign Region based on values in another field

[arnettmh]

Our company has sales people in the field that might have sales in various Regions.  I want to assign the Region based on Region that has the greatest number of sales for that particular salesperson.

For example, in the table below, I would want all of Salesperson A's sales assigned to the South Region, even though he has some sales in other Regions since he had the greatest number of sales in the South Region:

Salesperson	Region	Sales Amount
Salesperson A	South	1,000
Salesperson B	South	50
Salesperson A	West	12
Salesperson A	Central	55
Salesperson A	East	25
Salesperson B	Central	52

What kind of workflow could I use to come up with this?

Also, in the event of a tie between 2 Regions, I have a table that has a Rank on it that would then determine which Region would be used. I would also need this to be part of the workflow.

I am still relatively new to Alteryx and would appreciate any help given.

Thanks all!

[oly]

Hi @arnettmh,

Check attached workflow. Not sure if you wanted maximum or minimum ranking value as priority, I used the minimum. Hope the logic is very straighforward, just combination of joins and summarize tools.

Best,

Ooy

[arnettmh]

Thanks for the really quick response.  Unfortunately, I am unable to open the file as I am on an older version of Alteryx (2021.1).  Are you able to screenshot the workflow?

[arnettmh]

I was able to open the file now.  I feel like this "almost" gives me the solution I need and I really appreciate your help.  However, in the final output, I would need the Region with the most sales applied to that salesperson and their total sales summed up for that Region.

For example, using the sample numbers above, the final output would have Salesperson A assigned to the South region since that is the Region that had the largest sales amount and the total sales amount for Salesperson A would be 1,092.  For Salesperson B, they would be assigned to the Central Region since they had the most sales in that Region (52).

So, the final output would look like the following:

Salesperson	Region	Sales Total
Salesperson A	South	1,092
Salesperson B	Central	102

[DataNath]

How does this look @arnettmh? Have increased one of the sales values (Salesperson A - West) to match another (South) to show it working with the rank table as well:

[arnettmh]

This is awesome!  I like the approach.  What if I wanted to keep the original Region data (field named Region would become "Original Region") and add another column called (Max Amount Region), how would I do that?  Output would look like the below:

Salesperson	Original Region	Max Amount Region	Sales Amount
Salesperson A	South	South	1,000
Salesperson B	South	Central	50
Salesperson A	West	South	12
Salesperson A	Central	South	55
Salesperson A	East	South	25
Salesperson B	Central	Central	52

[DataNath · Accepted solution]

@arnettmh just add another join like so :) If you don't want the 'Sales Total' field in the output then simply untick it in the Join tool configuration:

[arnettmh]

Thanks for all the assistance!