Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Joining Multiple Fact Tables

Problem: These are the tables I have in the data model. What is the best approach to link them.

--------------------------- Salesforce -----------------------

OpportunityFact:

%Key1

%Key2

Opp Name

SF_FiscalYear

SF_FiscalMonth

Amount

Account:

%Key1

%KeyFact

Target Client Code:

Target Client

Product:

%Key2

Product Name

Competency

---------------------------------Revenue---------------------------

RevenueFact:

%KeyFact

Account Name

Head Client Name

R_Competency

R_Fiscal_Year

R_Fiscal_Month

Revenue

------------------------------------------------------------

I would like to link Salesforce Tables and Revenue Table based on composite key which includes

From Salesforce Tables:

%KeyFact + Competency + SF_FiscalYear + SF_FiscalMonth


From Revenue Fact Table:

%KeyFact + R_Competency+  R_Fiscal_Year + R_Fiscal_Month



Problem with various tried approaches:

Left Join: If I left join all tables from Salesforce, to create a single fact table it will create duplicates and result in double counting

Associated Link Keys: If I link them, I cannot create a composite key in Salesforce because each of the required fields is sitting in a separate table.

Any help is appreciated.

Sample Script For Testing:

----------------------------------------------------------------------------------------------------------------------------------------------

Opportunity:

Load * INLINE [

%Key1, %Key2, Opp_Name, SF_FiscalYear, SF_FiscalMonth, Amount

AA, 001, Opp1, 2017, Mar, 500

BB, 002, Opp2, 2017, Feb, 200

];

Account:

Load * INLINE [

%Key1, %KeyFact, Target_Client

AA, A001, BMO

BB, B001, Apple

];

Product:

Load * INLINE [

%Key2, Product_Name, Competency

001, Prod1, CompA

002, Prod2, CompB

003, Prod3, CompB

];

RevenueFact:

Load * INLINE [

%KeyFact, HC_Name, R_Competency, R_Fiscal_Year, R_Fiscal_Month, Revenue

A001, BMO, CompA, 2017, Mar, 200

A001, BMO, CompB, 2017, Feb, 400

];

1 Solution

Accepted Solutions
gouthamkk
Creator
Creator

Hi,

Try concatenating all tables

View solution in original post

6 Replies
Not applicable
Author

And if you use left join but adding a more key is:

%Key1 +%KeyFact + Competency + SF_FiscalYear + SF_FiscalMonth

Anonymous
Not applicable
Author

I guess using left join on fact tables like Opportunity (which contains dollars / amount) will result in double counting

Anonymous
Not applicable
Author

I have added a sample script so you can test your approach

gouthamkk
Creator
Creator

Hi,

Try concatenating all tables

Anonymous
Not applicable
Author

Create a link table that connects all the tables.

Somthing like this:

http://www.learnallbi.com/link-table-in-qlikview/

Anonymous
Not applicable
Author

Concatenating and naming similar fields (like sales_date & revenue _date)  with same name would solve the problem.

Link table is also an approach, but having a single fact table is always more efficient and better in terms of performance.