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

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"