topic Re: Facts and Single dimension in QlikView

Facts and Single dimension

Thu, 17 Apr 2014 14:38:16 GMT

I have two fact tables which are unrelated in my script but have common dimensions. One approach i though was the to rename the same fields in my second fact to something different to the first and then in the dim add another line which gives the link to the second fact. The other option im thinking is to do as described with the fact tables, but create a copy dim. Any thoughts or better approaches?

Cheers

Re: Facts and Single dimension

Colin-Albert — Thu, 17 Apr 2014 14:43:30 GMT

I would concatenate the two fact tables together then you will just have one fact table linked to your dimensions.

You could add a "source" field to the fact table if you need to differentiate where the data came from.

Re: Facts and Single dimension

Thu, 17 Apr 2014 14:46:19 GMT

The key thing though is i want to ensure that these two facts are not linked. Will this approach still be okay? I already 4 facts which are being concatenated but these are related.

Re: Facts and Single dimension

Colin-Albert — Thu, 17 Apr 2014 14:55:32 GMT

If the 2 facts have common fields names then you can either use the source field in your expressions to separate the data, or rename the fields so that the fields for the two concatenated facts are kept separate.

Obviously the common dimension(s) would have the same field names.

Re: Facts and Single dimension

Thu, 17 Apr 2014 15:01:58 GMT

So the two facts do have common fields and I have several dims that will be used by both, but I dont want the facts to be linked.

So lets say I have the following

FACT1:

Load FP, Actual, Cost

from Table1

FACT2:

Load Income, Profit, FP, SP

from table 2

DIMFP:

Date a FP

Re: Facts and Single dimension

Colin-Albert — Thu, 17 Apr 2014 15:09:48 GMT

FACT:

load

FP,

Actual,

Cost

from Table1 ;

concatenate(FACT)

load

Income,

Profit,

FP,

from table 2

You do not need a separate dimension table in this case, just add a listbox on the FP field.

Re: Facts and Single dimension

Thu, 17 Apr 2014 15:15:35 GMT

what i am finding with that though though is in the app everything appears in the FACT. I want to be able to select attributes from the second fact table easily, i.e from the fact2.

Re: Facts and Single dimension

Colin-Albert — Thu, 17 Apr 2014 15:23:21 GMT

You should not need to worry about whether you are selecting from table 1 or table 2

A chart with dimension FP and the expression sum(Profit) will only show data from Table2 as QlikView will supress blank values automatically so no data from Table1 will be in the chart.

Re: Facts and Single dimension

Thu, 17 Apr 2014 15:25:06 GMT

ah okay that makes sense. Thank you very much for this.