Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Help designing a link table

I have an odd structure and I am trying to figure out how to properly link up with multiple fields as the key in multiple tables.  any help/suggestions would be appreciated.

One could easily argue over what the fact and dimensions are as it changes depending on what question the users are asking, but below is my best explanation with their key fields.  Concatenating the key fields of each table will give a unique key for that table.  The Performance table may have more dates than either of the fact tables, the SecurityStatic table will have more distinct SecurityIDs than the fact tables, yet there are cases where I would want to treat these dimension tables as facts and have all of the data, not just those that have related data in either of the fact tables.

Fact Tables:


     BusinessDate; SecurityID; SecSourceID; PortfolioNo


     BusinessDate; SecurityID; SecSourceID; PortfolioNo



     BusinessDate; SecurityID; SecSourceID


     SecurityID; SecSourceID


     SecSourceID; PortfolioNo


     BusinessDate; SecSourceID; PortfolioNo



2 Replies
Not applicable

Re: Help designing a link table


I think you don't need a link table, try to concatenate Holdings Table and Transactions Table and the association with Dimension Tables will be natural. The resultant script can be something like this:





'Holdings' as Source

from Holdings.qvd;





'Transaction' as Source

from Transaction.qvd;

Not applicable

Re: Help designing a link table

I may eventually join the two, but for the sake of argument, let's assume that doesn't work with the analyses the business needs to perform.  Even if we were to assume that, I have key fields in the dimensions, but I have dimensions that have keys that aren't avialable in the fact table.  My goal is to have 1 single date field that will work across the application to make the user experience more consistent; however, there are different amounts of dates, and If i link the calendar to the holding table, i can't report on the data in the performance table that doesn't have a date in the holding table.

Community Browser