Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

How do I do a one to many join but pick up the highest ranking item only

Highlighted
5 - Atom

Hi, I'm new to Alteryx so my question is probably a basic one. I'm trying to join a list of clients to a list of agreements. However I only want to join the highest ranking agreement for an account and leave out all of the other lower ranking agreements. How can I do this?

Highlighted
5 - Atom

Hi, this worked, thanks.

Highlighted
16 - Nebula
16 - Nebula

If you sort the agreements by ranking you can then use a select tool grouped by account to keep only the highest.

 

Depending on the structure of your data you can do either before joining to the clients or after to filter just what you need.

Highlighted
13 - Pulsar

Use a Sort, then a Unique.  Unique outputs unique records (logical, right?), taking the first record found from top to bottom --- hence it's important to sort the data first.  Sort by the ranking field(s) so you end up with the agreements you want on top, then take a Unique on Account so it only returns the first agreement it finds for each account.  Note that if you need to use lower ranking agreements elsewhere in your workflow, you've got the duplicates stream also coming out of the tool.

 

image.png

 

Hope it helps!

 

Labels