Place results from expression on specific dimension attribute - without table relationship between fact and dimension

[Anonymous]

Hello

I'd like to place the results from an expression onto a specific dimension attribute - even without a table relationship existing between the dimension table and the table where I'm evaluating my expression on.

In SQL I'd do this with a CASE WHEN statement and hard-code the value of the specific dimension attribute like this:

How would I be able to reproduce the same results in a table in Qliksense?

I started with this:

=IF(COUNT({1<

                 AccountScheduleTotaling = {'11071'}

                >} AccountScheduleLineNo) = 1

   , 2295238395 //make this to an expression

   , ''

)

But if I replace the hardcoded value of 2295238395 with a SUM expression like "SUM([{< >}[Measure1])", i obviously get nothing on the row in the Table where AccountScheduleTotaling is equal to 11071. Because there is no relationship between my fact table and my dimension table.

If i insert "SUM({1}Measure1])" which ignores all filters, the measure column messes up the table and still doesn't place the results on the row where AccountScheduleTotaling = {'11071'}.

I want Qlik to ignore the relationship and in a way force the results on my expression "SUM({< +whatever filters i set here >}[Measure1])" onto the dimension attribute.

Message was edited by: Emil Hawwa Vissing

[petter]

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.

Thanks

[Anonymous]

Hi Petter

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

Three Dimensions:

- Dim_Account

- Dim_AccountSchedule

- Dim_Date

Two Facts:

- 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)".