topic Re: Re: Common Shared Calendar Modeling Challenge in QlikView

Common Shared Calendar Modeling Challenge

Thu, 12 Sep 2013 17:16:55 GMT

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.

Re: Common Shared Calendar Modeling Challenge

swuehl — Thu, 12 Sep 2013 17:23:36 GMT

Not sure I completely understand.

If I select a Start Date different from 11/18, I do get an effect on the other tables, filtered by A_ID = A.

Sure, you only filter your records in the tables by the linked A_ID, but since you don't have any other dates in your tables B and C, how do you wan these filtered when selecting a date range?

edit:

You can also have a look into this discussion and linked tutorial:

http://community.qlik.com/thread/66717

So, if you want to have a common calendar and you have in fact date fields in all your table (not only in the link table), then maybe try to concatenate tables A,B,C.

I read that you are coping with tables are coping with mixed granularity, so maybe this is also of interest:

http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/26/mixed-granularity

edit2: I'm sorry, haven't had a close look to your inline tables, I missed the dates.

Re: Common Shared Calendar Modeling Challenge

Gysbert_Wassenaar — Thu, 12 Sep 2013 17:37:20 GMT

See attached qvw. I've used the Qlikview Components library to create the link table. See the LinkTable.qvw document in Examples folder of the qvc-8.1.zip download.

Re: Common Shared Calendar Modeling Challenge

Fri, 13 Sep 2013 19:58:54 GMT

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:

Challenge:

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

-----------------------------------------------------------------------------------------

In this scenario I need to create a model where somehow the dates are related to the %LinkTable field but the attributes of the table still go through the common field "A_ID" on all tables.

So in step 4 above instead of filtering Table B on %LinkTable value of "5" it is filtered on A_ID of "A". If this was the case two rows would show for Table B (Pepper and Coconut) .

Re: Re: Common Shared Calendar Modeling Challenge

Gysbert_Wassenaar — Sat, 14 Sep 2013 11:03:05 GMT

I reckon you need a bit of set analysis for that. See attached qvw.

Re: Re: Common Shared Calendar Modeling Challenge

Sat, 14 Sep 2013 14:24:50 GMT

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!