Link Table?

[Nagaraju_KCS]

Hi Community,

How to create linktable,

LinkTableTemp:

LOAD C_SERVICE_CENTRE,

     C_SAMPLE_MONTH,

     C_BRANCH,

     FILE_NO,

     C_LOAN_PRODUCT

Resident Compliance_Report;

Concatenate

Load

FILE_NO,

BRANCH         As LinkBranch,

SERVICE_CENTRE As Link_ServiceCentre,

LOAN_PRODUCT   As Link_Product,

SAMPLE_MONTH   As Linkdate

Resident DatabaseData;

Concatenate

Load

FILE_NO,

F_BRANCH         As LinkBranch,

F_SERVICE_CENTRE As Link_ServiceCentre,

F_SAMPLE_MONTH   As Linkdate

Resident File_Details;

Concatenate

Load

FILE_NO,

QR_BRANCH         As LinkBranch,

QR_SERVICE_CENTRE As Link_ServiceCentre,

QR_SAMPLE_MONTH   As Linkdate

Resident Question_Review_Status;

Concatenate

Load

FILE_NO,

QS_BRANCH         As LinkBranch,

QS_SERVICE_CENTRE As Link_ServiceCentre,

QS_SAMPLE_MONTH   As Linkdate

Resident Question_Status_Report;

Concatenate

Load

FILE_NO,

CO_BRANCH         As LinkBranch,

CO_SERVICE_CENTRE As Link_ServiceCentre,

CO_SAMPLE_MONTH   As Linkdate

Resident Compliance_Overdue_Report;

LinkTable:

Load Distinct

  FILE_NO,

  Linkdate,

  LinkBranch,

  Link_Product,

  Link_Product                        As LOAN_PROD_CODE,

  Link_ServiceCentre,

  Link_ServiceCentre                  As SERVICE_CENTRE_CODE,

  year(Linkdate)                      As Year,

  Month(Linkdate)                     As Month,

  AutoNumberHash128(FILE_NO,Linkdate) As Link_Field

Resident LinkTableTemp;

DROP Table LinkTableTemp;

Thanks in advance.....

[Anonymous]

Can you send your sample file, Just wanted to take a look .

[Anonymous]

- Identify common fields between common tables

- Create concatenated Key

- remove common fields from source tables and move them onto link table which we have taken in link table

- load common table with key

[Nagaraju_KCS]

Hi jaswant,

sorry for the delay..

Above fields are common in my tables.

[tresB]

Please refer this nice  attached doc

[agni_gold]

If you want to create a main fact table then you have to concatinate in this way :

LinkTableTemp:

LOAD C_SERVICE_CENTRE,

     C_SAMPLE_MONTH,

     C_BRANCH,

     FILE_NO,

     C_LOAN_PRODUCT

Resident Compliance_Report;

Concatenate LinkTableTemp

Load

FILE_NO,

BRANCH         As LinkBranch,

SERVICE_CENTRE As Link_ServiceCentre,

LOAN_PRODUCT   As Link_Product,

SAMPLE_MONTH   As Linkdate

Resident DatabaseData;

Concatenate LinkTableTemp

Load

FILE_NO,

F_BRANCH         As LinkBranch,

F_SERVICE_CENTRE As Link_ServiceCentre,

F_SAMPLE_MONTH   As Linkdate

Resident File_Details;

Concatenate LinkTableTemp

Load

FILE_NO,

QR_BRANCH         As LinkBranch,

QR_SERVICE_CENTRE As Link_ServiceCentre,

QR_SAMPLE_MONTH   As Linkdate

Resident Question_Review_Status;

Concatenate LinkTableTemp

Load

FILE_NO,

QS_BRANCH         As LinkBranch,

QS_SERVICE_CENTRE As Link_ServiceCentre,

QS_SAMPLE_MONTH   As Linkdate

Resident Question_Status_Report;

Concatenate LinkTableTemp

Load

FILE_NO,

CO_BRANCH         As LinkBranch,

CO_SERVICE_CENTRE As Link_ServiceCentre,

CO_SAMPLE_MONTH   As Linkdate

Resident Compliance_Overdue_Report;

/* Now You can make final fact table */

LinkTable:

Load Distinct

  FILE_NO,

  Linkdate,

  LinkBranch,

  Link_Product,

  Link_Product                        As LOAN_PROD_CODE,

  Link_ServiceCentre,

  Link_ServiceCentre                  As SERVICE_CENTRE_CODE,

  year(Linkdate)                      As Year,

  Month(Linkdate)                     As Month,

  AutoNumberHash128(FILE_NO,Linkdate) As Link_Field

Resident LinkTableTemp;

DROP Table LinkTableTemp;

Or if your question is to link all the tables with each other then you can use lookup funtion and make some composite keys to link each other .

Hope this may help you.