community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.
SOLVED

Replace Duplicate Value based on Highest Sales

Atom

Hello,

 

I have data that contains duplicate names. All of the names have a unique identifier(RepID). I am able to use Fuzzy Match to choose the correct name and then Summarize to remove the duplicate. My issue is for duplicate names who have different Territories and Cities and/or States.

 

I need to choose the Territory, City and State of the duplicate with the highest YTD Sales and replace the row with the lower YTD Sales with the info from the row with the highest YTD Sales. I also include a Company Name but I sort to avoid names that are a part of multiple companies showing up near each other(duplicate names with different company names can be ignored).

 

I have been playing around with Fuzzy Match & Group and Multi Row Formulas and I am unable to get this to work. Currently this is a manual process for me so I would really appreciate any help. I've searched the community but nothing is my exact issue with the sales portion being the criteria.

 

I've attached a dummy workflow of what my actual data looks like and a screenshot below of what I need Thank you!

 
 

Orig.JPGOrig2.JPG

Highlighted
Alteryx
Alteryx

What about something like this? I did a group by RepID up top and because you have already sorted YTD descending, I just say if row-1:RepID is null meaning that it is the first row of the RepID, then just use the YTD value. Otherwise use the value above it. This will put the largest YTD value across every RepID. Workflow is attached. 

 

multi row.png

Alteryx Certified Partner

@BrandonB Not sure this is doing exactly as requested. It's just Territory, City and State that needs to be updated. Check the attached,

 

Capture.PNGworkflow

 

 

 

Alteryx
Alteryx

Ah, I think you might be right @PhilipMannering 

 

I interpreted "replace the row with the lower YTD Sales with the info from the row with the highest YTD Sales" as the actual sales value itself. 

Atom

I can't believe it was this simple all along! I'm always going straight to formulas instead of trying the other existing tools. I have been working on this for a while. 

 

Thank you @PhilipMannering !

Atom

This formula didn't help for this case, but it will be useful for another workflow I'm working on. Thank you for your response @BrandonB 

Labels