topic Help with association in QlikView

Help with association

Wed, 03 Jul 2013 15:51:51 GMT

I need to break down sales quotas by month. My quotas xls file has quota broken down by month as "qtMonth":

My master Calendar table has a "Month" field. In order to show the quota by month, I need to associate the qtMonth to Month, but I don't want an actual assocition in my model as my quotas table (FY14NewBusiness below) is associated by TerritoryID. You can see in the chart below that quotas are being summed for all months, and not individual. What's the best way to solve this?

Re: Help with association

Gysbert_Wassenaar — Wed, 03 Jul 2013 15:57:32 GMT

Perhaps you need to consolidate that table with another fact table. See this blog post: Fact Table with Mixed Granularity

Re: Help with association

Wed, 03 Jul 2013 16:04:50 GMT

Hi,

Try with a Bridge Table:

Tmp_BridgeTable:

Load distinct [Month] Resident Calendar;

Join

Load [TerritoryID], [qtMonth] Resident FY14NewBusiness ;

Bridge_Business_Calendar:

Load distinct [(Month], [TerritoryID]

Resident Tmp_BridgeTable

Where [Month] = [qtMonth] ;

Drop Table Tmp_BridgeTable ;

Regards.

Re: Help with association

Fri, 05 Jul 2013 16:33:02 GMT

Although this solution seemed like a good idea, it still caused a loop associated by Month to the Calendar table. It may make more sense to see the entire model:

Re: Help with association

Fri, 05 Jul 2013 19:51:34 GMT

Thanks for the reply Gysbert although I'm having trouble applying it to my situation. Can you expand on how generic keys could help me link my tables by month and territory please?