Matching Data to Event

[RSchomer]

Hello,

First time posting here.  I'm in a bit of an pickle and need a solution for a problem I'm experiencing.  I get a file with a list of names of instructors and students.  There are two students paired with one instructor.  Students have an assignment type of TR, while the instructors have the assignment type of IN.  Each event has a unique event ID which groups the three names together.  Also listed is a location and a start time.  

Sample raw data:

Event_ID	Name	Assignment	Location	Start
1	Smith, R	TR	RM1	7:00
1	Johnson, T	TR	RM1	7:00
1	Sawyer, T	IN	RM1	7:00
2	Bishop, J	TR	RM1	13:00
2	Jackson, P	TR	RM1	13:00
3	Freeman, G	TR	RM2	7:00
3	Taylor, R	IN	RM2	7:00
3	Thompson, F	TR	RM2	7:00

My desire is to get a workflow which lists each student along with his/her location, start time, partner, and instructor as shown below.

Desired results:

Name	Location	Start	Partner	Instructor
Smith, R	RM1	7:00	Johnson, T	Sawyer, T
Johnson, T	RM1	7:00	Smith, R	Sawyer, T
Bishop, J	RM1	13:00	Jackson, P
Jackson, P	RM1	13:00	Bishop, J
Freeman, G	RM2	7:00	Thompson, F	Taylor, R
Thompson, F	RM2	7:00	Thompson, F	Taylor, R

I am a beginner to the Alteryx application and have absolutely no idea how to make this happen.  I'm not even sure if it's possible.  Any advise would is greatly appreciated.

[MarinaMi · Accepted solution]

Hi RSchomer

Please see attached for the solution. The steps to take are as follows:

1. Use the filter tool to separate the instructors from the students.

2. Use the join tool and join on Event ID to have an Instructor field for each student

3. Union the Left and Inner Joins to include students that have no instructors

4. Each event appears to have two students.

5. Use the Summarize tool and Group By "Event ID" and First "Name". This will give you the first student in each event.

6. Use the Join tool with the output from the Summarise tool as Left and the output from the Union tool as Right join and join on Event ID. Rename the Name field from the Right join to Partner.

7. Similar to steps 5 and 6, do the same but instead of "First", choose "Last" in the Summarize tool.

8. Use the Union tool to combine the outputs of the two joins.

9. This will have some rows that you don't need with the same name in both "Name" and "Partner" fields. Use the filter tool to remove them from your data with the formula "Name != Partner"

[RSchomer]

MarinaMi, 

Thank you for the assistance.  Although I didn't see an attachment I followed the steps you provided and was able to accomplish my goal.  The only difference between your steps and mine is that I got a little tripped up on steps 5-7.  Instead of summing by First "Name" I did a GrouBy for both Event_ID and Name.  I then joined the result of the Summarize tool and the previous Union tool.  It Joined all my data together.  I then used the Filter tool with the formula you recommended and then coupled it with the Select tool to get my desired results.  

As a follow up question, is it a more robust design to use your method where I use multiple Summarize tools or will my way work?  I just fear that my method could leave me open to unforeseen flaws which could result in potential loss of data.