I've been struggling with this concept for some time now and am almost at a point of giving up, hoping somebody has an idea. My dilemma is that I'm trying to create a common calendar from multiple date fields in different tables. I've included a sample of my challenge in this post.
The three tables I created in the sample all share a common field A_ID. Table B and table C go to a lower grain with their own dates and unique keys (B_ID and C_ID). Selecting specific dates, or a range of dates, from Calendar Link has no effect on Table B or C because relationship is going through higher level (A_ID instead of B_ID or C_ID).
Does anybody have any ideas on how I can model this differently so data will be filtered appropriately? Ive tried several different methods but haven't had any success. Any help would be MUCH appreciated. Thanks in advance.
G Wassenaar - First thank you very much for responding and including the library. I had no idea this existed and I plan on using it going forward in other projects. I tested your idea and it works to a point. I would greatly appreciate it if you, or anybody else reading this, could open the attached .qvw and go through my "Challenge" box. I hope this makes sense and there is a way to model this. It's like I'm trying to create a link table with tables at different grains, but they share the same field at the highest grain. Here is the "Challenge" I put in the .qvw sample:
1) Select Jan 1 1998 from "Start Date" Calendar Object
2) The link table works as expected, filtering out rows from both Table B and C
3) Select "Apples" from C_NAME field
4) Table B goes blank since %LinkTable value of "5" doesn't relate to Table B
G Wassenaar - Again I very much appreciate your ideas. Yes set analysis would work as you've highlighted in your example, but imagine in my non-sample .qvw I have 8 dimensions which will share a common link table, each with multiple attributes. Each measure in each object would have a huge set analysis statement. The maintenance and performance would take a hit.
I would really hope somehow a QlikView model could handle this. You might agree that the issue is the three tables are naturally related by a high level key, but the link table needs to be created at the lower level to share date attributes. It seems as though I've tried everything but what about concatenating or joining the three tables vs creating a link table, or contatenating or joining in conjunction with a link table? I appreciate the help!