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

Identify the highest value of an attribute

blitz45
8 - Asteroid

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!

 

ProductIDVendorIDValue
00120ABC123$150,000.00
00120SAM444$136,000.00
00120LOL576$7,512,000.00
00120GGH285$114.75
00125SKL786$135.44
00125SIL412$376,000.00
00125LOP589$1,576,888.45
00125QWE453$7,562.30
00125ASO756$20.00
00125XDZ576$365,785.46
00232LHD567$346,857.46
00232MJL891$15.46
00232LDF435$5.00
5 REPLIES 5
JosephSerpis
17 - Castor
17 - Castor

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.

Kohlmeyl
5 - Atom

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!

NicholasM
Alteryx Alumni (Retired)

@blitz45 

 

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. 

blitz45
8 - Asteroid

This is exactly what I needed! thank you! 

blitz45
8 - Asteroid

This is PERFECT! Thank you!

Labels