Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Data Modelling With Multiple Fact Tables

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.

Sync.jpg


Tags (2)
9 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Data Modelling With Multiple Fact Tables

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

Highlighted

Re: Data Modelling With Multiple Fact Tables

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

HIC

Highlighted
Master II
Master II

Re: Data Modelling With Multiple Fact Tables

join both table where columns are same

Highlighted
Not applicable

Re: Data Modelling With Multiple Fact Tables

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

Highlighted
Master II
Master II

Re: Data Modelling With Multiple Fact Tables

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

Highlighted
Not applicable

Re: Data Modelling With Multiple Fact Tables

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

Highlighted
Master II
Master II

Re: Data Modelling With Multiple Fact Tables

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

Highlighted
Champion
Champion

Re: Data Modelling With Multiple Fact Tables

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

Highlighted
Creator III
Creator III

Re: Data Modelling With Multiple Fact Tables

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;