This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have a record set where Column A contains unique identifiers, Column B contains a string, and Column C contains a numerical value. However, in the record set, there may be duplicates in Column A. What I'd like to do is merge the duplicate rows so that the unique identifier is retained, as well as the string in Column B where the numerical value was higher in Column C.
(Note: Rows 1 and 2 were originally part of separate tables, and were unioned.)
In this case, I want to retain "Dog" from Row 1 because the numerical value is 100 but add the 10 from Row 2. The end result should transform to this:
I can do this in SQL using SUM and CASE WHEN clauses and I'd imagine I'm using the Summarize and/or Filter nodes, but I can't quite figure it out.
The Summarize tool calaculates the total sales per unique ID. You can use Sort to order the data by ID, and then by the value field with highest on top. A sample tool, grouped on the ID field, will pull the record with the highest value per record ID (using First N records and set N to 1).
Join back together, and you have your answer. Attaching a sample workflow because I'm unable to post pictures today.