0

I resolved my fact table with this post (Handling multiple fact tables in Qlikview). But I have a problem with entity tables. I will use the example in this post (Handling multiple fact tables in Qlikview) to explain my problem:

test_scores_fact     |    enrollment_fact      |    school            |    gender         |   student
----------------     |    ---------------      |    ------            |    ------         |   ---
school_code (FK)     |    school_code (FK)     |    school_code (PK)  |    gender_id (PK) | student_id (PK)
test_code (FK)       |    grade_id (FK)        |    school_name (FK)  |    gender_desc    | school_code (FK)
grade_id (FK)        |    ethnicity_id (FK)    |    address           |    ...            | gender_id (FK)
gender_id (FK)       |    gender_id (FK)       |    ...               |
ethnicity_id (FK)    |    number_enrolled (F)  | 
student_id(FK)      |
test_score (F)       |

In the example i added the student table that have a relation with school table and geneder table.

My problem:

i followed the steps, then i have loaded my sql tables in Qlik Sense. I have also created a report for test_scores_face table with some filters like (student_id, gender_id,...). The problem that when i have selected the filter student_id the report can not filter by it? I think that the problem in the entity tables (school, gender, student) because they have common fields (like school_code, geneder_id). These common fields produce a circular references.

To avoid these circular references I have to do another link table for entity tables or collgate these table to the link table of fact tables? I tired these 2 solution but I also can not filter by student_id!

How can i deal this problem?

Community
  • 1
  • 1
ciao_jo
  • 11
  • 3
  • There could be a ton of causes for the issue you mention. Here are some things to check: Make sure filter box (list box) and chart are in the same Alternate State, ensure that one of the common fields in your `test_scores_fact` links to `student_id` through the link table. Test that the data model is set up correctly by trying to filter on one of the common fields like `gender_id`. – bdiamante Oct 13 '16 at 17:59
  • I edited my post to explain the problem well. I think that the problem is that these common fields produce a circular references. To avoid these circular references I have to do another link table for entity tables or collgate these table to the link table of fact tables? I tired these 2 solution but I also can not filter by student_id! any help? thanks – ciao_jo Oct 14 '16 at 09:41

1 Answers1

1

You need to remove the link between the tables, I have assumed that you are manually editing the script?

If the field is not required for link

  • Remove/Rename the field that is causing the circular reference

If the fields are required for linking, concatenate the fields in the tables using &'_'&

It depends on how you want to organise your data, but for a start it looks to me like grade and gender could be removed from the enrolment fact table as a start as this is covered on the student.

Chris J
  • 938
  • 1
  • 8
  • 27
  • I tired to add all ids of table in the link table by concatenate them using &'_'& beacase i need them as filter in my Sense APP, but the problem is still not resolved. I could not understand it. For example, if i want to filter by gender_id in the entrolment table, it not working. I do not know how could i resolve it. Thanks. – ciao_jo Oct 15 '16 at 20:18