Identifying Duplicates, sum numbers associated with duplicates

[sswift13]

I'm working with the data below to identify duplicates based on the "full part number" and want to sum all "part rates" associated with those "full part numbers".

Example: for 7L14-1040-AA0028, each row would should say "94.44%". I want to keep duplicates in the file.

How can I recognize duplicates, not remove them, and then sum the "part rate" for all of the duplicate parts without summing an entire column?

[apathetichell]

unique tool can get you only unique values - you can branch off- unique and then use a summarize.

You'll also need to change your percentages into doubles via  multi-field formula with a nifty

tonumber(replace([part rate],"%","")) and change the type to double.

[sswift13]

I understand that I can separate them, do two different formulas, and then join them back together, but how would I write a formula to sum the "take rate" of all matching "full part numbers"? 

[atcodedog05]

Hi @sswift13 

The way to go would use summarize tool groupby "full part number" and sum of "take rate"

[atcodedog05]

Hi @sswift13 

Here is a resources where you can learn more on it. https://community.alteryx.com/t5/Interactive-Lessons/Summarizing-Data/ta-p/424417 

[apathetichell]

as @atcodedog05  said summarize tool on group-by setting will give you a sum of percent field once you've converted it to a double. Then you can rejoin the two values so they you have one datastream with two columns with two distinct values.

If you need help - post an .xlsx of some data and someone will post a workflow.

[sswift13]

I used the summarize tool, however, when I bring it back together and try to merge the part rates into one column, it is adding up to be too much, usually in the hundreds. Even moving the decimal place does not get the correct number.

[atcodedog05]

Hi @sswift13 

It seems to working for me and is very low values.

Hope this helps 🙂

[apathetichell]

Sorry - here's what I get when I use a summarize and rejoin strategy with the data you uploaded. Is this close to what you want?

[atcodedog05]

I guess i forgot on the rejoining part @apathetichell 😅

Hi @sswift13 

Here is a variation to the above joining approach. I am using find & replace tool which works like excel vlook up.

Hope this helps 🙂