Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Replace Duplicate Value based on Highest Sales

Lauren_G
5 - 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

5 REPLIES 5
BrandonB
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

PhilipMannering
16 - Nebula
16 - Nebula

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

 

workflowworkflow

 

 

 

BrandonB
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. 

Lauren_G
5 - 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 !

Lauren_G
5 - 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