Data Modelling With Multiple Fact Tables

[#1]

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.

[marcus_sommer]

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

[hic]

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

HIC

[er_mohit]

join both table where columns are same

[#5]

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

[er_mohit]

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

[#7]

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

[er_mohit]

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

[datanibbler]

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

[Anonymous]

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;