topic Re: Data Model question - Circular reference in QlikView

Data Model question - Circular reference

Sun, 26 Oct 2014 23:36:27 GMT

I have below tables in the database with relationships as shown in the model diagram. It's obvious that simulating same model in QV will result in circular reference. To give little background on the business model:

Courses will have Activities (Course, Activity)

Students can enroll into a Course (Enrollment)

There can be activities related to enrollment (Enrollment_Activity)

I can convert this into a classic Dimensional model with Course, Student as dimensions and Enrollment_Activity (left join enrollment_activity, activity) as fact table and entirely eliminate enrollment, activity tables from the model but that would loose activities where there are no records in enrollment_activity table. For example data would be missing if users wants to see all Activities related to a course irrespective of enrollments.

I don't see merging or link table as options in this case. Please let me know if there are alternatives that wouldn't result in circular reference yet preserving all the reference data.

Re: Data Model question - Circular reference

jagan — Mon, 27 Oct 2014 02:34:51 GMT

Hi Sridhar,

Try like this

Student:

LOAD

FROM Student;

Course:

LOAD

FROM Course;

Enrollment:

LOAD

Course_Course_ID,

Course_ID,

FROM Enrollment;

Activity:

LOAD

Course_Course_ID AS Course_ID

FROM Activity;

LEFT JOIN(Activity)

LOAD

Activitiy_Activity_ID AS Activity_ID,

Enrollment_Enrollment_ID AS Enrollment_ID,

FROM EnrollmentActivity;

Hope this helps you.

Regards,

Jagan.