Log In
Register
Home
May 6, 2022
Offline
Good day forum members. I have task, which i have sought for solutions, in other forums,buh i need an efficient method.
The challenge is, i have a huge dataset, this data set are students records frequently coming from the web.The column "count" as shown in the image below, will count how many subjects written by each student. As a rule the subjects count shouldnt exceed "2" buh some students have erroneously or deliberately written 3 and more. Please, how can i dynamically remove some selected column values corresponding to a particular StudentID to make the "count" column read 2, in this case, instead of manually removing subject scores, which to me will be a huge task and a burden .
However, the problem will this method, is which column values corresponding to that "StudentID" should be removed. Should i create a list of accredited subjects for each student? so that each student cant take more than the required. I dont know how to go about this.
This is the link to the challenge file: https://1drv.ms/x/s!Akd5adcSw6.....T?e=cip5du
Please note: The column Count uses a formula to count "text" items in the row, to determine number of subjects written.
The camth, exammth,mth,mthgrade are test, exam , total for (test+exam), and lettered grade for that subject mathematics,(mthgrade) respectively, same also for other columns, with crs, eng.
Thank you for anticipated solutions
See Answer
November 8, 2013
Offline
Hi Femco,
If the subjects vary a lot between subject, makes sense to set the allowed subjects by student.
If not, then you can set a subject priority/importance, then in PQ we can preserve only the first 2 responses corresponding to the top 2 subjects.
Or, you can set the 2 subjects list per Class.
May 6, 2022
Offline
HI @Catalin,
How do i achieve this . How to set priority, subject list etc. please help!
The goal is to force each student take 2 required subjects....i am using 3 subjects in all for demo purpose, buh in reality, it may be not more than 9 subjects.
So how do i make accreditation for subjects?
following the link above:
say; ID:1002 accredited to offer Mathematic, CRS, but not English => OK since Count column doesnt exceed 2
ID: 1003 accredited to offer Mathematics, English but not CRS=> not ok buh replaced CRS for English, Count Column doesnt exceed 2, good buh offering wrong subjects combination.
ID:1007 accredited to offer Mathematics, English, but not CRS=> Not ok, since Count Column has exceeded 2, and Subject written is more than accredited.
November 8, 2013
Offline
First you have to decide what you need then we will setup a table similar to one of the tables below.
Which one fits: setting a list of subjects per group (Class), or per student?
| settings per group | |
| SS1 | math |
| SS1 | crs |
| SS2 | eng |
| SS2 | math |
| SS3 | crs |
| SS3 | eng |
| settings per student | |
| 1001 | math |
| 1001 | crs |
| 1002 | eng |
| 1002 | math |
| 1003 | crs |
| 1003 | eng |
May 6, 2022
Offline
@Catalin, i will prefer the latter..Setting per Student.
November 8, 2013
Offline
Hi Femco,
Here is an example, note the Transformation step that clears the 3 grade columns if the subject is not in the list of Accredited subjects from settings:
{"mthgrade", each if List.Contains(row[Accredited][Accredited],"mth") then row[mthgrade] else null},
{"enggrade", each if List.Contains(row[Accredited][Accredited],"eng") then row[enggrade] else null},
{"gradecrs", each if List.Contains(row[Accredited][Accredited],"crs") then row[gradecrs] else null}
} ))),
You can modify as many columns you need to.
Keep in mind that all students must be found in settings. The ones not in settings will have their grades cleared.
Answers Post
1 Guest(s)
