Intersection Table

[#1]

Hello people,

I have a course dimension, and a fact table that indicates the students who are enrolled on those courses.

I would like to have a table which indicates the number of intersections of student enrollments between the courses, that is, something like this:

I have tried to do it with a dynamic table, but I cannot specify the same dimension twice, and even if I could, I don't know if I would achieve this structure.

Anybody have a clue about this?

Best Regards,

Ygor

[#2]

In fact, I can specify the same dimension twice as a calculated dimension, but it does not make the possible intersections between it, just the intersection with itself...

[johnw · Accepted solution]

I THINK the attached is what you're looking for.

[#4]

Hello John,

Thank you for your reply, but I still have the Personal Free Edition, and I can't open that file 😞

If it does not cause you much trouble, could tell me briefly how you made it?

Regards,

Ygor

[#5]

Did you calculated the indicator in the load script or managed to somehow generate it in a dynamic table?

[johnw]

Heh, you probably shouldn't mark something as solved until you've actually SEEN a solution. 🙂

Anyway, here's the load script from the example file:

[Table 1]:
LOAD
Order
,Product
,subfield(Variant,',') as Variant
INLINE [
Order Product Variant
1 A X1,Y5,K4
2 A X2
3 A Y4,Y5
4 A Y5,K4,K5
5 B X1,X2,Y4,Z7,Z2
] (delimiter is ' ')
;
[Table 2]:
NOCONCATENATE
LOAD
Order
,Variant as Variant2
RESIDENT [Table 1]
;

So the idea is that you create two tables linked by a key, the key you're planning to count (Order in the example, Student for your data, I believe). The second table should have a copy of the field you're trying to compare to itself (Variant in the example, Course for your data, I believe). Then for your chart, you'd have Course and Course2 as the dimensions, and count(distinct Student) as your expression. Something along those lines anyway if I understood the requirement.

[#7]

Ups, I didn't think about the "verify answer button" as marking a question as solved, but to mark a good answer 🙂 Lesson learned!

And your hints are once again correct, thank you again!