Trying to circumvent and avoiding the associations in a Qlik Data Model will seldom give you a good performing application. Also trying to replicate a SQL too closely without taking advantage of the associative model might lead to overly complex and underperforming applications.
If you explain exactly which tables you have and what you are trying to achieve of output without specifying how you want it done will probably give you better suggestions from the forum....
Do you have some limited test data for the tables involved with all the essential fields/columns? Having them in an Excel workbook would be quite ok.
Thank you for your help. I understand that avoiding associations is definitely not the qlik best-practice way of doing this.
I've attached a QVF file with a small portion of data imported + a separate excel workbook (source) which has the limited data set as well. If you open the QVF file, please use the the filter pane that is inserted on the sheet and choose this value "Account Schedule - 001 (Active)"
The data model consist of the following
- Fact_Finance (has direct link to Date + Account)
- Fact_Production (has direct link to Date only)
One mapping table:
- Dim_Account_AccountSchedule_Map (This enables a link between AccountSchedule and Fact_Finance)
With this model I can set up an account schedule in a table in Qlik and see the Total Finance Amount split across the accountschedule lines. So far so good.
We also have another table however, Fact_Production, which has values that in business terms depicts "WIP" amount. These WIP amounts have not been realized, hence no amount can be placed on an account yet.
But I still want to place the total of a set of the rows in Fact_Production to the account schedule line which has the Totaling equal to 11071, and another set to where the totaling is 11072. These two sets might overlap, which is why I cant force a relationship between Fact_Production and Dim_Account in my datamodel. I'd had to duplicate the Fact_production table and it would cause too many rows. The data set you are seeing is a small fraction of the entire model.
Makes sense? Let me know if this could be solved in other ways
Notice the filter on AccountScheduleName. It's set to "Account Schedule - 001 (Active)".