Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

marishnagendran
Contributor

Data Model

Dear All,

 

Customer Master Branch Fact Item MasterCalender
         
Cust_ID Branch_ID Cust_ID ItemCode %Creation_Date
Customer_Name Branch Name Branch_ID ItemGroup Fiscal Year
    %Creation_Date   Quarter
    ItemCode   Month
    Qty    
    Profit    
    NetProfit    
         
BusinessPlan_Fact        
         
%Creation_Date        
ItemGroup        
Cust_ID        
Branch Name        
Target        
Potential        

 

Above tables are my table structure.I want to do a data model for this.Need you help

2 table (i.e) Business plan and fact table is in separate table.

I tried many ways it generates synthetic key or circular reference.Need your help

7 Replies
Partner
Partner

Re: Data Model

Hi,

First, get your facts in the middle. Then try to link all your fields to this fact table. 

  1. Concatenate the Business Plan Fact to the Fact.
  2. Customer Master -> Cust_ID
  3. Branch -> Branch_ID
  4. Item -> ItemCode
  5. MasterCalendar -> %Creation_Date

Every name that is then double in your tables, that is causing synthetic keys or circular references, rename them!

For example: Branch name from BusinessPlan_Fact -> Branch name as BusinessPlan.BranchName.

This way you don't have any duplicates.

Jordy

Climber

Work smarter, not harder
marishnagendran
Contributor

Re: Data Model

Thanks for your response

Yeah that's the issue i'm facing

but for business plan fact i want to analyse by itemgroup and branch name.

Because i want to compare business plan facts with fact table facts.

For example

ItemGroupPotentialTargetQty
A1008020
B2009010
Partner
Partner

Re: Data Model

Yes that is possible then, please execute the steps and you will have a good model that you can use.

Jordy

Climber

Work smarter, not harder
marishnagendran
Contributor

Re: Data Model

I don't know whether it is possible or not??

If it is possible kindly share the steps how to do

Partner
Partner

Re: Data Model

First model the steps that I have presented, that is the guide to your solution.

Jordy

Climber

Work smarter, not harder
marishnagendran
Contributor

Re: Data Model

If i go with your step(i.e Concatenate 2 fact tables).the following scenario happens

From ItemMasterTable

I'm using Itemgroup wise Potential it won't works.because in Fact table itemcode is linked 

 

Partner
Partner

Re: Data Model

I get the problem. For this you can left join your Item table:

Left Join(Fact)
Load distinct
ItemCode
ItemGroup
From [YourSource](qvd);

This way you won't have this problem.

Jordy

Climber 

Work smarter, not harder