Report by Course

[Grail030510]

Good Day everyone,

I would like to ask for your help or way how to implement this on alteryx workflow, First Image shows a table of the current status of students regarding each specific subject and their status such as Completed, Ongoing or Not Started.

STUD_ID	Subject	Status	Course
101	Math1	Completed	CS
101	Science	Completed	CS
101	English	Completed	CS
101	Reading	Completed	CS
101	Social	Completed	CS
101	Chemistry	Completed	CS
102	Science	OnGoing	IT
102	Math1	Completed	IT
102	English	Completed	IT
103	Reading	Completed	NURSING
103	Social	OnGoing	NURSING
103	Math1	OnGoing	NURSING
104	Reading	Completed	Teacher
104	Algebra	Completed	Teacher
104	Culture	Completed	Teacher
104	Religion	Completed	Teacher

Now The Other Table shows what are the subjects that they need to take in order for them to finish their course.

Course	Subject
CS	Math1
CS	Science
CS	English
CS	Reading
CS	Social
CS	Chemistry
CS	C Prog
CS	.net
IT	Science
IT	Math1
IT	English
IT	Java
IT	PE
IT	Reading
IT	Social
IT	Religion
IT	DBMS
IT	AI
NURSING	Reading
NURSING	Social
NURSING	Math1
NURSING	Chemistry
NURSING	Biology
NURSING	Neurons
NURSING	Dental
NURSING	System
TEACHER	Reading
TEACHER	Algebra
TEACHER	Culture
TEACHER	Religion
TEACHER	PE
TEACHER	Math1
TEACHER	Science
TEACHER	Social

How can I create a logic workflow that would make an output that would look like this, such as all the subjects that are not yet taken will be tag as "NOT STARTED"

STUD_ID	Subject	Status	Course
101	Math1	Completed	CS
101	Science	Completed	CS
101	English	Completed	CS
101	Reading	Completed	CS
101	Social	Completed	CS
101	Chemistry	Completed	CS
101	C Prog	NOT STARTED	CS
101	.net	NOT STARTED	CS
102	Science	OnGoing	IT
102	Math1	Completed	IT
102	English	Completed	IT
102	Java	NOT STARTED	IT
102	PE	NOT STARTED	IT
102	Reading	NOT STARTED	IT
102	Social	NOT STARTED	IT
102	Religion	NOT STARTED	IT
102	DBMS	NOT STARTED	IT
102	AI	NOT STARTED	IT
103	Reading	Completed	NURSING
103	Social	OnGoing	NURSING
103	Math1	OnGoing	NURSING
103	Chemistry	NOT STARTED	NURSING
103	Biology	NOT STARTED	NURSING
103	Neurons	NOT STARTED	NURSING
103	Dental	NOT STARTED	NURSING
103	System	NOT STARTED	NURSING
104	Reading	Completed	Teacher
104	Algebra	Completed	Teacher
104	Culture	Completed	Teacher
104	Religion	Completed	Teacher
104	PE	NOT STARTED	Teacher
104	Math1	NOT STARTED	Teacher
104	Science	NOT STARTED	Teacher
104	Social	NOT STARTED	Teacher

Thank you so Much In advance

[DataNath]

Hey @Grail030510, here's an approach you could use. To roughly outline the steps I've used:

1) Make the [Course] Uppercase to match table 2 (so we can conduct a Join)

2) Summarize to Group by [STUD_ID] and [Course] to get a distinct list

3) Conduct a Join to give us which subjects each [STUD_ID]+[Course] combo actually needs

4) Conduct a second Join to see which of these already exist i.e. have been completed

5) Those that fall out of this Join don't exist i.e. are 'NOT STARTED' so we therefore assign this label in a Formula expression

6) Use a Union to stack this data back together, the completed and not started records

7) Sort tool to tidy the order up

Hope this helps!