Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Mr_small_t
Contributor III
Contributor III

Where exists (...) in load script

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

Labels (1)
1 Solution

Accepted Solutions
jensmunnichs
Creator III
Creator III

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!

View solution in original post

2 Replies
jensmunnichs
Creator III
Creator III

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!

Mr_small_t
Contributor III
Contributor III
Author

That is brilliant!

Thank you for the swift reply!

Regrads,
Torbjørn