Icon

Course Tabulation - configurable rules

Course Tabulation

There has been no description set for this workflow's metadata.

Break the rules out into three lookup tables.Each table lists the "course rule" along with either:The required set of CourseThe required set of EntititesThe required set of Grades The manually entered rule list was all in one table to make it easier toenter, but we want to turn each comma separated string into its own"list" as these can then be ungrouped into their own lookup tablesThe required set of CourseThe required set of EntititesThe required set of Grades For each student/course result row in the main data table, join firstlyto the Course Rules that match for the course they are taking, andthen match to the Entities and Grades lookups for that "course rule"At the end of this, we have appended to each row the set of courserules that are applicable to the current course, along with the requiredattributes specified for the "course rule" The rule engine compares the entityid with required entity id andthe classGrade with the required grade on each line. If theymatch, it returns Y. If they don't it returns NThe Row Filter then removes anything that is N The upper GroupBy groups all of the courses passed for eachStudent for each "Course Rule"The lower GroupBy groups all of the courses required by each"Course Rule"The joiner then joins these two.Only where there is a match can we say that a student has passedall of the necessary courses required to pass a given Course Rule Pivot the data so that the Course Rule headings become columns, witheither a YES for those that passed, or NO for those that haven't Bring the tabulated rule outcomes back into the main table of students and course, then tidy up... and ... class-dismissed! Component tosort selected columnsinto order(The set of overall courses passed)Sample DataRemove the originaltabulation columnsOutputFilters to onlyWSH MET Y or NManually enteredCourse Rules ConfigurationCourseListGradesEntitiesJoin student's courseto course rulesNode 46This is nowthe lookup tablefor course rulesCourses LookupGrades LookupEntities LookupThis is nowthe lookup tablefor gradesThis is nowthe lookup tablefor entitiesJoin required Gradesfor each course "rule"Join in requiredentitiesfor each course "rule"Mark individual coursesthat passed against"rules"Remove thosenot marked as apassGet sorted listfor each course-rulethat has a passedcourse foreach studentSorted Listof coursefor each Rule setTop:Students whohave fully passeda course-ruleBottom:is course rulesthat have not matchedto any studentTabulateSet to YESTabulate the outputwith courses that have been passedTabulateSet to NOAdd in theoverall coursesbased on rules not yetachievedFill in the missing NOvaluestidy upSelected ColumnAlphaNumeric Resorter Excel Reader Column Filter Table View Column Filter Table Creator Cell Splitter Cell Splitter Cell Splitter Joiner Column Rename(Regex) Ungroup Column Filter Column Filter Column Filter Ungroup Ungroup Joiner Joiner Rule Engine Row Filter GroupBy GroupBy Joiner Pivoting ConstantValue Column Joiner Pivoting ConstantValue Column Cross Joiner Missing Value RowID Break the rules out into three lookup tables.Each table lists the "course rule" along with either:The required set of CourseThe required set of EntititesThe required set of Grades The manually entered rule list was all in one table to make it easier toenter, but we want to turn each comma separated string into its own"list" as these can then be ungrouped into their own lookup tablesThe required set of CourseThe required set of EntititesThe required set of Grades For each student/course result row in the main data table, join firstlyto the Course Rules that match for the course they are taking, andthen match to the Entities and Grades lookups for that "course rule"At the end of this, we have appended to each row the set of courserules that are applicable to the current course, along with the requiredattributes specified for the "course rule" The rule engine compares the entityid with required entity id andthe classGrade with the required grade on each line. If theymatch, it returns Y. If they don't it returns NThe Row Filter then removes anything that is N The upper GroupBy groups all of the courses passed for eachStudent for each "Course Rule"The lower GroupBy groups all of the courses required by each"Course Rule"The joiner then joins these two.Only where there is a match can we say that a student has passedall of the necessary courses required to pass a given Course Rule Pivot the data so that the Course Rule headings become columns, witheither a YES for those that passed, or NO for those that haven't Bring the tabulated rule outcomes back into the main table of students and course, then tidy up... and ... class-dismissed! Component tosort selected columnsinto order(The set of overall courses passed)Sample DataRemove the originaltabulation columnsOutputFilters to onlyWSH MET Y or NManually enteredCourse Rules ConfigurationCourseListGradesEntitiesJoin student's courseto course rulesNode 46This is nowthe lookup tablefor course rulesCourses LookupGrades LookupEntities LookupThis is nowthe lookup tablefor gradesThis is nowthe lookup tablefor entitiesJoin required Gradesfor each course "rule"Join in requiredentitiesfor each course "rule"Mark individual coursesthat passed against"rules"Remove thosenot marked as apassGet sorted listfor each course-rulethat has a passedcourse foreach studentSorted Listof coursefor each Rule setTop:Students whohave fully passeda course-ruleBottom:is course rulesthat have not matchedto any studentTabulateSet to YESTabulate the outputwith courses that have been passedTabulateSet to NOAdd in theoverall coursesbased on rules not yetachievedFill in the missing NOvaluestidy upSelected ColumnAlphaNumeric Resorter Excel Reader Column Filter Table View Column Filter Table Creator Cell Splitter Cell Splitter Cell Splitter Joiner Column Rename(Regex) Ungroup Column Filter Column Filter Column Filter Ungroup Ungroup Joiner Joiner Rule Engine Row Filter GroupBy GroupBy Joiner Pivoting ConstantValue Column Joiner Pivoting ConstantValue Column Cross Joiner Missing Value RowID

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: BATEB031
Created at: 2022-10-19
On NodePit since: 2022-10-21
Last update: 2024-11-21
Created with KNIME version: v4.6.3

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!