Association of 2 tables: Link / Join / Merge?

[dmohanty]

Hi Friends,

I have two set of tables in Excel File. Wanted to load them in QlikView. But not sure how to Link up those 2 tables.

Is it better to Join or create a Link Table? Could someone please help me on this? Also please help me on how to separate Fact and Dimension table from this source and also to create a Date Table?

Attached is the source table (with table structure and dummy data). Could not figure out on how to create a KEY based on this.

Also between the two tables, these are the common fields:

SBU

Geo

Final Vertical

Cust Name (from table 1) = SAP Customer Name (from table 2)

Project Type  (from table 1)= Pricing (from table 2)

Com Code (from table 1) = SAPCompanyCode (from table 2)

Used fields from Table 1:

REVENUE:

LOAD
     Month,
     QUARTER,
     [Final Vertical],
     SBU  ,
     [Cust num],
     [Cust Name],
      Geo,
     [Proj Type],     

[Emp Name]    ,
     [Project Classification],
     [Onsite BMMs],
     [Offshore BMMs],
     [Total BMM],
     [Onsite rate],
     [Offshore rate],
     [Onsite revenue],
     [Offshore revenue],
     Reimbursement,
     [Pass through],
     FROM

(qvd);

Used fields from Table 2:

ORDERBOOKING:

LOAD Geo,
     Region,
     [Account Name],
    [Account Owner],
     [Account Owner Employee Number],
     [Account Owner Role],
     [Account Classification],
     SBU,
     Vertical,
     [Sub Vertical],
     [Opportunity Number],
     [Opportunity Name],
     [Opportunity  Owner],
     [Delivery Manager],
     [Delivery Manager Emp no],
     [Project Start Date],
     [Project End Date],
     [SAP Customer Name],
     [SAP Customer Number],
     [SAPCompany code],
     [Order Value TCV (USD)],
     [Order Value TCV (Trans Curr)],
     [Order Number],
     Pricing,
     [Order Creation Date],
     [Fin Year (SOW\ Purchase Order Date)],
     [Project  Trigger],
     [PO Date],
     [Order Approval Date],
     [Approved By],
     [Amendment/Order],
     [Amendment No],
     [OM %],   
     [Order/Amendment Type],
     [SL BDM],
     [SL BDM Employee Number],
     [SL BDM Role],
     [Practice  Value(USD)],
     [Practice  Value      (Trans Curr)],
     [Practice ACV (USD)],
     [Pricing ID]FROM

[#2]

Hi,

In this case I would use the concatenate statement and flag each source of data, then in your expressions call depending of what you need, Ex: in the load I would add a flag field let say 1 as [_SourceFlag] in your first table and 2 as [_SourceFlag].

Then use this flag in your expressions, for example: sum({$<_SourceFlag = {1}>}[Total BMM]) / count({$<_SourceFlag={2}>}[Order Number]).

To separete your facts and dimensions you can Load one time the revenue but only choose you facts and create a Key (maybe using the contatenation of some fields) and then load again the revenue adding that same key to but only choosing your dimensions fields, that way you separate facts and dimensions using the same file, for the calendar I would recommend use the example of Developer Qlikview document.

Hope this can helpy you.

[dmohanty]

Thanks for some help Juan.

Still could you show some help in script to create Key or separate Dimensions and Facts?

[its_anandrjs]

1.Check your model which is the most unique key then connect the two tables.

2.Take a table from existing table and create only date fields there and make as a calender table and link with date key.

Hope this help

Thanks & Regards

[dmohanty]

Hi All,

Based upon above help and suggestions, I have used Link Table concept to associate the above two tables.

Also created a Master Calendar in the script.

Could anyone please help me in verifying the attached excel which contain scripts for Facts, Link Tables and Data Model?

Please help me more in letting it forward.

Regards!!

[#6]

Each case is a little bit different and "perfecting data model" may be hard work. Especially if you have application with big data volume. Sometimes you may want to add some preaggregations or flags in the script. Sometimes you will have to decide in which table some field should be if you know if you want to have only last value or maybe if this attribute is historical.

It is rather difficult to check if your model is good for somebody who dont know what you would like to do in your application.

try to load your test data into first model version and prepare most important expressions. If your expressions looks to complicated or you have not good response time, consider if your model is good....

Look Here:

Perfect Your QlikView Data Model

regards

Darek