Alteryx Multiple Look Ups

[Tyler_]

Hello Alteryx Community,

I have a problem that has left me stumped and I am hoping someone here has the insight to overcome it. My data in its simplest form resembles the following:

Data
Project ID	Group ID	Name
Project A	123	Bob
Project B	234	Jane
Project C	345	Peter
Project D	456	Barb
Project E	567	Luke
Project F	678	Dave
Project G	789	Sarah
Project H	890	Julie

I have a data file that has multiple Group IDs and the name of a user currently assigned to that group. I need to return many combinations of names depending on their group number to show a hierarchy. However, while the Group ID's will remain static from a logic perspective, the names will change. Therefore, I need to have an output that looks like Bob, Jane, Peter but is tied to Group ID 123, 234, 345 so that if Jane is replaced with Alex tomorrow, the output would then automatically change to look like Bob, Alex, Peter with Alex being the name associated with Group 234. I will have close to a hundred of these combinations but the output would need to look something similar to:

Output	Logic
New Column	New Column
Jane, Barb, Sarah	Option 1 - Name of Group ID 234, Name of Group ID 456, Name of Group ID 789
Luke, Bob	Option 2 - Name of Group ID 567, Name of Group ID 123
Luke, Dave, Jane, Julie	Option 3 - Name of Group ID 567, Name of Group ID 678, Name of Group ID 234, Name of Group ID 890
Dave, Peter, Barb	Option 4 - Name of Group ID 678, Name of Group ID 345, Name of Group ID 456
	etc.

Where the Output column is what the data output would look like but it is driven by the Logic so that if Names change they will maintain the display based on the new names associated with the Group ID's. Based on my research this likely involves Join tools but I cannot figure out the right combination to generate the desired outcome.

Any help would be greatly appreciated.

Tyler

[WernerE]

.

Hi Tyler, 

Hoping I understood your issue correctly, the attached build should allow you to set the column logic and run a similar file as your first image which should give you the new column breakdown as per the logic. 

Let me know if this is what you were looking for. 

[Tyler_]

Hi Werner,

Yes, that appears to be what I was looking for.

Quick follow up question, what would I need to change to have the logic simply read "234, 567, 789" etc. instead of "Group 1 - Name of Group ID 234, Name of Group ID 567, " etc.? I noticed that you had used the RegEx tool to parse out the numbers but I have never used that tool before and it throws an error if I use anything except that text for the logic instead of a string of numbers separated by commas. If you can help me there I believe I am set.

[WernerE · Accepted solution]

Hi Tyler,

I've updated the flow to give you two flows so you can pick which one works for you. I've also included details on how the Regex code works so hopefully it helps you see how it used the code to extract only what you want.  

Feel free to let me know if you have any other questions.

[Tyler_]

Hi Werner,

That was immensely helpful! Thank you very much for both the detail and explanation, it is appreciated. This should give me everything that I need now.

Hope you stay safe in this strange year.