Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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
Highlighted
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

Highlighted
Contributor III
Contributor III

That is brilliant!

Thank you for the swift reply!

Regrads,
Torbjørn