Vlookup / Join only for first match

[Zapata_123]

Hi all,

I just switched from Excel to Alteryx and would like to conduct  a lookup on a dataset but would like to return only the first match. Returning all matches, as the join function does, unfortunately leads to a huge number of entries that my laptop cannot handle (as the source file is quite big already).

Consider the simple example attached: 

-We have data on two families and want to identify the ID of the parent of each person (in this example, only one parent is listed for each person)

-We can do this by calculating the parent generation first (a person's generation minus one)

-After that, in excel I would do a lookup on the parent ID and the person's ID, starting in the current row. That returns the next parent (for children) or the next grandparent (for parents). As excel returns the first match only, this is the correct matching.

How can I do a lookup in Alteryx that returns the first match starting from the current row only?

Any suggestions are welcome.

Stefan

[Joe_Mako · Accepted solution]

How about something like the attached?

I have also included a more complex sample data.

- Record ID to generate a record sequence identifier
- Multi-Row Formula to add a Family ID based on when Generate is 1
- Formula to add the field Parent Generation
- Join with self on Family ID and Parent Generation to Generation, keeping just the fields needed
- Filter out potential Parent record that are prior
- Sort by Record ID and Parent Record ID
- Select to keep just the first record per Record ID
- Join Multiple to bring the full data back in

[Zapata_123]

Thanks Joe-Mako, your workflow works perfectly :-)