Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
I hope someone can help me on this one. Thank you in advance.
I have one table with information about previous students. This is table "Course_Participated", and it contains information about the student id number (Part_id), year of study (Yr), Course id (Course_id) and the exam result on the course (Mark).
Then I have a second table with information about those who applies for different courses this year. This is table "Course_applied", and it contains information about the student id number (App_id), which course she applies (App_course_id) and, as each person can apply for more than one course, the variable App_nr is an indicator for number of application per person.
App_course_id and Part_id is unique for each person. This means that if App_course_id equals Part_id then it is the same person.
My question: I want to create a new table similar to the table Output below. This table should only include the id number of the applicantans which has participated in a course before, and it should include the year the person participated and the mark.
Looking forward to replies from the community!
Regards,
Torbjørn
Course_Participated:
LOAD * INLINE [
Part_id, Yr, Course_id, Mark
1, 2015, dk, P
2, 2016, ls, F
2, 2017, dk, Q
2, 2018, as, F
2, 2019, dk, P
3, 2019, dk, F
4, 2019, ss, P
5, 2018, ss, P
];
Course_applied:
LOAD * INLINE [
App_id, App_nr, App_course_id
2, 1, ss
2, 2, dk
2, 3, ma
3, 1, dk
3, 2, ss
7, 1, ss
7, 2, na
7, 3, ma
];
Output:
LOAD * INLINE [
ref_id_OUTPUT, App_nr_OUTPUT, App_course_id_OUTPUT, Participated_Before_OUTPUT, Mark_Before_OUTPUT
2, 2, dk, '(2017, 2019)', '(Q,P)'
3, 1, dk, '(2019)', '(P)'
];
Hi,
Maybe something like this (see attached)?
I assumed that by this:
App_course_id and Part_id is unique for each person. This means that if App_course_id equals Part_id then it is the same person.
You meant that App_id is unique for each person, not App_course_id, as this does not give me the results in your output table. Also, your result for App_id/App_nr 3/1 I think might be wrong, as I get a mark of F not P, which I believe is correct as person 3 never got a 'P' mark according to your first table.
As for the script, I inner joined the 2 tables together to only leave Ref_id's that appear in both Participated/Applied. Then I used the concat() function to combine Yr/Mark into one field, and grouping by Ref_id/App_nr/Course_id to only have 1 line per combination.
Hope this is what you were looking for!
Hi,
Maybe something like this (see attached)?
I assumed that by this:
App_course_id and Part_id is unique for each person. This means that if App_course_id equals Part_id then it is the same person.
You meant that App_id is unique for each person, not App_course_id, as this does not give me the results in your output table. Also, your result for App_id/App_nr 3/1 I think might be wrong, as I get a mark of F not P, which I believe is correct as person 3 never got a 'P' mark according to your first table.
As for the script, I inner joined the 2 tables together to only leave Ref_id's that appear in both Participated/Applied. Then I used the concat() function to combine Yr/Mark into one field, and grouping by Ref_id/App_nr/Course_id to only have 1 line per combination.
Hope this is what you were looking for!
That is brilliant!
Thank you for the swift reply!
Regrads,
Torbjørn