topic Re: Data Modelling With Multiple Fact Tables in QlikView

Data Modelling With Multiple Fact Tables

Fri, 06 Sep 2013 10:55:33 GMT

Hi All,

I am developing a QlikView Dashboard based on Multiple Fact Tables. Since They are using Same Dimension Tables but different Key columns.It creates Sync Table. Please help me to avoid this issue.

Re: Data Modelling With Multiple Fact Tables

marcus_sommer — Fri, 06 Sep 2013 11:00:44 GMT

You could concatenate the fact-tables or create a link-table or simply rename the fields which should not associated. Which way is the best depends from your data and aims.

- Marcus

Re: Data Modelling With Multiple Fact Tables

hic — Fri, 06 Sep 2013 11:19:44 GMT

Concatenating them is usually the best solution. See more on Fact Table with Mixed Granularity and on Generic keys

HIC

Re: Data Modelling With Multiple Fact Tables

er_mohit — Fri, 06 Sep 2013 11:32:26 GMT

join both table where columns are same

Re: Data Modelling With Multiple Fact Tables

Thu, 12 Sep 2013 10:23:56 GMT

The Measures are different.They use some common dimensions.So i cant merge these two tables.

Re: Data Modelling With Multiple Fact Tables

er_mohit — Thu, 12 Sep 2013 10:30:24 GMT

then use concatenate and generate a flag for both tables and use that flag in your expression side where ever you want

Re: Data Modelling With Multiple Fact Tables

Thu, 12 Sep 2013 10:50:02 GMT

Thanks Mohit.Can you please explain me in detail since i dont know what you mean by flag.

Re: Data Modelling With Multiple Fact Tables

er_mohit — Thu, 12 Sep 2013 11:30:18 GMT

see my demo attached file with excel data also relating to create flag

Re: Data Modelling With Multiple Fact Tables

datanibbler — Thu, 12 Sep 2013 11:45:46 GMT

Hi Krishnan,

you could also, if that's possible in your case, just rename all the fields except the keyfields - and if those are still several, build a compound_key and rename the original keyfields so they don't associate anymore.

I had the same issue once with two lists I was to display in QlikView - for simplicity, they were using the same column_names - so, since I wanted two separate tables in my data_model, I had to rename all the fields. You can have a different name displayed in diagrams and list_boxes, so there's no problem there. It's just a bit time-consuming once.

Best regards,

DataNibbler

Re: Data Modelling With Multiple Fact Tables

Anonymous — Thu, 12 Sep 2013 11:55:58 GMT

Hi,

You can make a primary key with all the common dimension for exemple:

Table 1:

Load

Field1&' - '&Field2&' - '&Field3 as %KEY,

Date,

Qty

FROM

XXXX

;

Table 2:

Load

Field1&' - '&Field2&' - '&Field3 as %KEY,

Amount,

Color

FROM

XXXXXX

;

Then the 2 tables will be joined using the field KEY wich contains the 3 fields

Then you can make a table containing the details of the 3 fields wich will be:

KEY_DETAIL_TABLE:

LOAD Distinct

KEY,

subfield(KEY,' - ',1) as Field1,

subfield(KEY,' - ',2) as Field2,

subfield(KEY,' - ',3) as Field3,

RESIDENT

Table1;

LOAD Distinct

KEY,

subfield(KEY,' - ',1) as Field1,

subfield(KEY,' - ',2) as Field2,

subfield(KEY,' - ',3) as Field3,

RESIDENT

Table2;