topic Where exists (...) in load script in QlikView

Where exists (...) in load script

Mr_small_t — Sat, 16 Nov 2024 01:03:41 GMT

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)'
];

Re: Where exists (...) in load script

jensmunnichs — Fri, 13 Mar 2020 14:16:43 GMT

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!

Re: Where exists (...) in load script

Mr_small_t — Fri, 13 Mar 2020 14:28:45 GMT

That is brilliant!

Thank you for the swift reply!

Regrads,
Torbjørn