Hi Everyone!
I'm wondering if anyone know how I should create a workflow that will identify the highest value of a ProductID and will output the VendorID (ie. Fee)? For example, the highest value of ProductID 00120 is $7,512,00.00 with VendorID LOL576, highest value for ProductID 00125 is $1,576,888.45 with VendorID LOP589 and so on. I would like to add a new column and output the VendorID that has the highest value. Any solutions would be awesome! Thank You!
ProductID | VendorID | Value |
00120 | ABC123 | $150,000.00 |
00120 | SAM444 | $136,000.00 |
00120 | LOL576 | $7,512,000.00 |
00120 | GGH285 | $114.75 |
00125 | SKL786 | $135.44 |
00125 | SIL412 | $376,000.00 |
00125 | LOP589 | $1,576,888.45 |
00125 | QWE453 | $7,562.30 |
00125 | ASO756 | $20.00 |
00125 | XDZ576 | $365,785.46 |
00232 | LHD567 | $346,857.46 |
00232 | MJL891 | $15.46 |
00232 | LDF435 | $5.00 |
Solved! Go to Solution.
Hi @blitz45 I mocked up a workflow that I think answers you questions I identify the max value then join back onto your original data to identify this row is the max value.
Hello Blitz,
Based on your question I wasn't sure if you were looking to slim your data down to only the rows that had the highest value per product, or if you simply wanted to append the vendor with the highest value per product, but both examples are in the following workflow.
If you are having trouble with Alteryx treating your value column as numbers, try putting it through the data cleansing tool to remove any punctuation. Then a select tool can convert it to a float.
By sorting your data by product type first, and value second, you can re-order your data with your highest vendor at the top of each product group of rows. From this point, a Unique tool on product will return only the rows with the highest value (VendorID is carried through). Otherwise, a multi-row tool can be used to bring the vendorID corresponding to the highest value to all other rows with the same product.
I hope this helps!
Attached is my workflow that should answer your question. It is very similar to the one @JosephSerpis posted, I just used an additional Summarize to find the Total for Each Product ID.
Let me know if you have any questions.
This is exactly what I needed! thank you!
This is PERFECT! Thank you!