Report by Course

Question

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_IDSubjectStatusCourse101Math1CompletedCS101ScienceCompletedCS101EnglishCompletedCS101ReadingCompletedCS101SocialCompletedCS101ChemistryCompletedCS102ScienceOnGoingIT102Math1CompletedIT102EnglishCompletedIT103ReadingCompletedNURSING103SocialOnGoingNURSING103Math1OnGoingNURSING104ReadingCompletedTeacher104AlgebraCompletedTeacher104CultureCompletedTeacher104ReligionCompletedTeacher

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

CourseSubjectCSMath1CSScienceCSEnglishCSReadingCSSocialCSChemistryCSC ProgCS.netITScienceITMath1ITEnglishITJavaITPEITReadingITSocialITReligionITDBMSITAINURSINGReadingNURSINGSocialNURSINGMath1NURSINGChemistryNURSINGBiologyNURSINGNeuronsNURSINGDentalNURSINGSystemTEACHERReadingTEACHERAlgebraTEACHERCultureTEACHERReligionTEACHERPETEACHERMath1TEACHERScienceTEACHERSocial

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_IDSubjectStatusCourse101Math1CompletedCS101ScienceCompletedCS101EnglishCompletedCS101ReadingCompletedCS101SocialCompletedCS101ChemistryCompletedCS101C ProgNOT STARTEDCS101.netNOT STARTEDCS102ScienceOnGoingIT102Math1CompletedIT102EnglishCompletedIT102JavaNOT STARTEDIT102PENOT STARTEDIT102ReadingNOT STARTEDIT102SocialNOT STARTEDIT102ReligionNOT STARTEDIT102DBMSNOT STARTEDIT102AINOT STARTEDIT103ReadingCompletedNURSING103SocialOnGoingNURSING103Math1OnGoingNURSING103ChemistryNOT STARTEDNURSING103BiologyNOT STARTEDNURSING103NeuronsNOT STARTEDNURSING103DentalNOT STARTEDNURSING103SystemNOT STARTEDNURSING104ReadingCompletedTeacher104AlgebraCompletedTeacher104CultureCompletedTeacher104ReligionCompletedTeacher104PENOT STARTEDTeacher104Math1NOT STARTEDTeacher104ScienceNOT STARTEDTeacher104SocialNOT STARTEDTeacher

Thank you so Much In advance

Completion.xlsx

DataNath · Answer

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!

Report by Course.yxmd